This half works - bit strange really.
We receive the Date flags from another system, so they flag “Date[Current FY]” = “Y” and "Date[Current Month] = “Y”. This, in theory, would mean that we are consistent across those two dimensions with other systems.
I was hoping then, that on the dashboard we would report a series of cards for YTD and Current Month that show the primary KPI’s and remain static while Date selections are made across the rest of the metrics. Allow the user to dig around while keeping the primary measures close to mind. Sometimes the user gets lost in the drilling and filtering.
So to do that I tried two measures:
A) Sales FYTD (Filter) = CALCULATE([Total Sales],FILTER(‘Date’,‘Date’[Current FY]=“Y”))
B) Sales FYTD (Filter) = CALCULATE([Total Sales],FILTER(‘Date’,‘Date’[Current FY]=“Y”))
Now A all the time and B is intermittent. So on the dashboard, attached below, if I choose different dates from the slider, A as a card (TOP LHS) remains constant which is good, but B, in the Working Table is only correct when the correct year is chosen.
That would be OK but when I try to create the same logic for Current Month, using the syntax for A, I try:
Sales This Month = CALCULATE([Total Sales],FILTER(‘Date’,‘Date’[Current Month]=“Y”))
which swaps Current Year for Current Month, and it doesn’t calculate the same as for YTD. If I select the current Month Year (March FY 18) it is correct as shown in the Working Table and the top card in Current Month. If I change the Month Year combination it becomes Blank.
Not sure what is wrong with the syntax?
Then when I try Sales same period last year it all goes wrong. I have this:
Total Sales LY CARD = CALCULATE([Sales FYTD (Filter)],SAMEPERIODLASTYEAR(‘Date’[Date]))
I call it CARD as I know I will need dynamic measures as well as Card specific measures. I thought that BI would know the dates it used in CALCULATE([Sales FYTD… and look for the same dates last year but apparently not. I also tried DATEADD -1 Year, but no luck there.
Card Example.pbix (2.1 MB)