Forum Discussion
Data Between Custom Week Start and Week End Date
In my report i have created Week Start and Week End date and for my organization the Week Start Date is Monday and End Date is Sunday, This Table Represent count for each Custom Weeks
in the table above we have only data until 3/24/2019, but the current date is 27 March 2019 i.e. Week Start Date =3/24/2019 and Week end Date=3/31/2019, since there are no count for this current week it's not showing on the table.
I am looking for way where i can show the Ticket Count CFY= 5 in a card in this week's report, this report is used every monday hence next week (3/24/2019 to 3/31/2019) the card should show data only for 3/24/2019 to 3/31/2019 even if the count is zero.
The above card data should show only last week (3/18/2019 to 3/24/2019) data even if i refresh the data.
hi, Anonymous
Just adjust the measure formula as below:
Measure3 = VAR __max = CALCULATE(MAX('Table'[Week End Date]),FILTER('Table','Table'[Week End Date]<TODAY())) RETURN SUMX(FILTER('Table',[Week End Date] = __max),[Count])You could use Current Day instead of TODAY()
Best Regards,
Lin
3 Replies
Seems like you could grap the MAX of the "My WeekEndDate" column and then FILTER down to that row essentially. So, something like:
Measure = VAR __max = MAX([My WeekEndDate]) RETURN SUMX(FILTER('Table',[My WeekEndDate] = __max),[Ticket Count CFY])Thank your for the response, Yes that makes sense when we don't have data for the Current week,Let me explain againIn the below example i have data for Week Ending 31 March as well and in this scenario if i run the report on 28th March 2019 i should see only data for 3/18/2019-3/24/2019 i.e. 5, the Data for 03/25/2019 to 03/31/2019 should only be reflected in the report when i run refresh the data on 04/01/2019.
hi, Anonymous
Just adjust the measure formula as below:
Measure3 = VAR __max = CALCULATE(MAX('Table'[Week End Date]),FILTER('Table','Table'[Week End Date]<TODAY())) RETURN SUMX(FILTER('Table',[Week End Date] = __max),[Count])You could use Current Day instead of TODAY()
Best Regards,
Lin
