Forum Discussion

mikebrodt's avatar
mikebrodt
Icon for Advocate I rankAdvocate I
2 years ago

Previous Date Measure for a New Card Visual

I have a bit of an interesting scenario. I am using the new Card visual in a report to show the number of licenses that have been used for a given date. I am using one measure to calculate the number of licenses based on a table that has each server, the license they have assigned, and a report date. The measure is the sum of a column and then I filter it with the max report date:

 

Corporate IT Billable Datacenter Host Licenses = CALCULATE(SUM('Billable Hosts'[Datacenter Licenses Required]), 'Billable Hosts'[Report Date] = MAX('Billable Hosts'[Report Date]), 'Billable Hosts'[Line of Business] = "CORPORATE IT", 'Billable Hosts'[License by Rules] = "DATACENTER")
 
Since the new Card visual supports adding in another measure, I am trying to add in the previous month's value. I created a second measure that calculates that: 
 
Previous Corporate IT Billable Datacenter Host Licenses = VAR OldDate = CALCULATE(MAX('Billable Hosts'[Report Date]),FILTER(ALL('Billable Hosts'[Report Date]),'Billable Hosts'[Report Date] <> MAX('Billable Hosts'[Report Date]))) RETURN CALCULATE(SUM('Billable Hosts'[Datacenter Licenses Required]), 'Billable Hosts'[Report Date] = OldDate, 'Billable Hosts'[Line of Business] = "CORPORATE IT", 'Billable Hosts'[License by Rules] = "DATACENTER")
 
The tables in the model are connected to a Report Date Dimension table.
 
The challenge I am having is when I try to filter the visuals in the report. I made a card with those two measures, and then created a Report Date Slicer using the Report Date Dimension table. The Slicer is set to use the Show All option and multi-select with Ctrl.
 
  • If the Select All is selected, everything works fine.
  • If I de-select Select All and instead select a single date, nothing updates
  • If I de-select Select All and instead select two consecutive dates, everything updates

I don't know if this is an expected behavior, but I don't think it would be. I have other reports that use a similar setup, but without the dual measure cards and they adjust accordingly when the date slicer selection is modified.

 

My goal is to have both the current and previous date values update when a single date is selected from the slicer. 

 

4 Replies