I'm trying to calculate the user logged in count on system.
In my table i have
Id, TRANSDATE now i want to calculate the measure on four categories
which are yesterday, last 7 days, this week till day, this month till day count.
Yesterday =
CONCATENATE(
CONCATENATE(
"Yesterday Count",
UNICHAR(10)
), " (" &
FORMAT(TODAY - 1, "DD-MMM")
& ")"
)
Last 7 Days =
CONCATENATEN(
CONCATENATE(
"Last 7 Days Count",UNICHAR(10)
),
" (" &
FORMAT(TODAY() - 7,"DD-MMM")
& " to " &
FORMAT(TODAY() - 1,"DD-MMM") &")"
)
This Week till date =
CONCATENATE(
CONCATENATE(
“This Week till date Count”,
UNICHAR(16)
), SWITCH(
TRUE(),
FORMAT(
if (
WEEKDAY(TODAY(),1) == 1,
TODAY(),
TODAY() - (WEEKDAY(TODAY(),1) - 1))
,"DD-MMM") ==
FORMAT(TODAY(),"DD-MMM"), " ("&
FORMAT{TODAY() ,"DD-MMM") &“)",
FORMAT(
if (
WEEKDAY(TODAY(),1) == 1,
TODAY(),
TODAY() - (WEEKDAY(TODAY(),1) - 1)
),"DD-MMM") <>
FORMAT(TODAY(),"DD-MMM")," ("&
FORMAT(
if(
WEEKDAY(TODAY(),1) == 1,
TODAY(),
TODAY() - (WEEKDAY{TODAY(),1) - 1)
),"DD-MMN") &" to “&
FORMAT(TODAY(), "DD-MMM") &")"
)
This Month till date =
CONCATENATE(
CONCATENATE(
"This Month till date Count”,
UNICHAR(10)
), SWITCH(
TRUE() ,
FORMAT(DATE(
YEAR(TODAY()),
MONTH(TODAY()),
1),
"DD-MMM"
) ==
FORMAT(
TODAY(),
"DD-MMM"
), “ ("&
FORMAT(
TODAY(),
"DD-MMM"
) &")",
FORMAT(
DATE(
YEAR(TODAY()),
MONTH(TODAY())
,1
),
"DD-MMM") <>
FORMAT(
TODAY(),
"DD-MMM"
)," ("&
FORMAT(
DATE(
YEAR(TODAY()),
MONTH(TODAY()),
1),
"DD-MMM") &" to “&
FORMAT(
TODAY() ,
"DD-MMM" &") "
)
I have resolved in this following way
YesterdayM =
CALCULATE(
DISTINCTCOUNT('user’[Id]),
FILTER('user’, [TRANSDATE] = TODAY() - 1)
) + 0
LastSevenDaysM =
CALCULATE(
DISTINCTCOUNT(‘user’[Id]),
FILTER(‘user‘,[TRANSDATE] >= (TODAY() - 7) && [TRANSDATE] < TODAY())
) + 0
CurrentWeekTillDateM =
CALCULATE(
DISTINCTCOUNT( 'user’ [Id]),
FILTER(’user',[TRANSDATE] >=
if(
WEEKDAY(TODAY(),1) == 1,
TODAY(),
TODAY() - (WEEKDAY(TODAY(),1) - 1)
)
)
) + 0
CurrentMonthTillDateM =
CALCULATE(
DISTINCTCOUNT(‘user‘ [Id]),
FILTER(‘user‘, [TRANSDATE] >=
DATE(
YEAR(TODAY()),
MONTH(TODAY())
,1)
) + 0