Get counts from data not part of the slicer's selection

[plaplant]

Hi, I hope someone can help with this.  It seems like it should be an easy one but I'm not getting the results I need.   

I have an Excel report I receive each month.  I simply add the new data to the master report and add a new date in a 'Report Date' column.   For example I had 2,044 rows of data from February and then I added 2,049 rows of data for March and added 3/1/2021 in the Report Date column.  

In PowerBI I have that Report date as a slicer and the current value would be 3/1/2021 so I can see March results.  If someone wants to see February they can select that Report Date in the slicer.  Very basic.

What I would like to do is have a card display the delta between the current month count and the prior month.   My slicer is set to 3/1/2021 so this would be the delta between March and February.  (5 in this case) Once I add April data, and assuming I'm choosing 4/1/2021 in my slicer, the card would show the difference between April and March.   

Can someone provide some suggestions of what measures I might create to accomplish this or if there are other changes I need to make to set this up?  I tried setting some items up but the resulting card is blank because it seems like it is only allowing or seeing the March data and I'm not getting access to February data since the slicer is set to 3/1/2021.  I'm relatively new to PowerBI as I've only been playing around with it since the first few weeks of March.

I appreciate any help.    

[Jihwan_Kim]

Hi, @plaplant 

Please try to write the measure like below in order to see the previous month's information.

previous month result =
CALCULATE ( currentmeasure, DATEADD ( calendardatecolumn, -1, MONTH ) )

If you select March, the above measure will show Feb's result.

In this case, the expected outcome can be achieved by writing the below measure.

Outcome = currentmeasure - previous month result

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[plaplant]

Hi Jihwan, Thanks for the quick response.  I unfortunately don't have anything I can share but basically I'm just looking to get a count of the rows for the data with a report date of 3/1/2021 minus the count of the rows for the data with a report date of 2/1/2021.    In your example above, what would you be using as Current Measure?      I tried adding something like this.   

Available Count Previous Month = CALCULATE(COUNTROWS(u_Headcount_file),DATEADD(u_Headcount_file[Report Date],-1, MONTH))

however the results are showing up as (Blank)

[plaplant]

Jihwan, it must have something to do with the date field I'm using in my slicer.  I actually have 4 different reports that have a similar set up that I'm bringing in.   I have a central calendar table and I'm joining each of the reports to this calendar on the Report Date field.   I'm then brining one of the fields from the Calendar table in as the Slicer because I want to be able to have all 4 reports adjusted based on the same date change.   I can try and set up a demo file and share that if you let me know how and where to post it.  I'm not sure where to put the information but I can certainly created a dummy file joining to the calendar file the same way.  

[Jihwan_Kim · Accepted solution]

Hi, @plaplant 

Thank you for your feedback.

If you have a dummy pbix file, you can save it in googledrive, onedrive, or dropbox, and you can share the link here.

If you have a dim-calendar-table, then try the below for finding the previous month's information.

Available Count Previous Month =
CALCULATE (
COUNTROWS ( u_Headcount_file ),
DATEADD ( [your calendar table's date column], -1, MONTH )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[plaplant]

Thanks Jihwan, I changed it to the date from my calendar table and that did the trick.  I was just pulling from the wrong table.   This would have originally worked for me as well if I had just chosen the correct table.   I still have a lot to learn.