Previous Date Measure for a New Card Visual

[mikebrodt]

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. 

[gmsamborn]

Hi @mikebrodt 

Very tough without a model or data but I noticed one thing.

In your [Previous Corporate IT Billable Datacenter Host Licenses] measure, have you tried changing <> MAX( to < MAX( ?

[mikebrodt]

Thanks for the suggestion! I tried, but I am still having the same issue. As for the model, it is really just the Servers table connected to a Data table. It is a One-to-Many relationship.

[gmsamborn]

Hi @mikebrodt 

Is there any way you can come up with sample data and your expected result based on the sample data?

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

I hope this helps.

Also, please add @gmsamborn to your reply or I might miss it.

[mikebrodt]

I think I figure dout what I need to do... I was able to replicate the way I want this to work by adjusting the measure to filter the date by the Date Dimension table instead of the date column in the fact table. Then it worked. Let me put together a sampel to upload with examples of it set up both ways for experimentation and to valida what I am seeing is the way it should work.