Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Getting data of previous month.

Hi team,

Capture.PNG

 

I've following date columns monthwise which are not fixed. Any date can be start day of the month. I've snapshots of every month.

So when i'm selecting a date from the slicer, it should show the repsective count of that month in my card. Which i can do for current month. But I also want the data for previous month, and previous of previous month.

Here i've selected june 24, so i'm getting count for this selected month, but i want count of may 27 and aprill 22 in respective remaining cards.

 

Thanks in advance

 

1 ACCEPTED SOLUTION

@Anonymous  Thanks for providing the sample data. Please try to do the same for any queries in future.

 

Here is the solution based on the test data provided.

 

PreviousMonthCount = CALCULATE(SUM('PBI - sample data'[Count of Assistants]),PREVIOUSMONTH('PBI - sample data'[FORECAST_START_WEEK_DATE]))
PrevioustoPreviousMonthCount = 
CALCULATE(SUM('PBI - sample data'[Count of Assistants]),PREVIOUSMONTH(PREVIOUSMONTH('PBI - sample data'[FORECAST_START_WEEK_DATE])))

image.png

 

Let me know if there is any further issues....





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

5 REPLIES 5
PattemManohar
Community Champion
Community Champion

@Anonymous Not sure whether you have tried this way...

 

1. Create a Date dimension table, where you will have an entry for each day and have a relationshipt with datekey from dimdate to your source table (where you have forecast_start_week_date).

 

2. Then create a measures as you need, count of cases for previous month using PREVIOUSMONTH function. Also, please make sure you use date field from DimDate as your slicer.

 

Hope this makes sense and let me know if you need any further information. Also, it will be always helpful if you post the sample data if you are looking for an accurate solution.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

Hi @PattemManohar 

 

Your solution won't work as i dont want the calender table here. If I created calender table then i wont be able to select the required dates only. i have snapshot data for those date. 

Hence i want given dates in the filter. Plus previousmonth funtion will give me value for the actual previous month according to calender date. 

Here if my current selected date is June 24 then in 2nd card for previous month it should give snapshot of 27th may(count of 27th may). 

I've attached sample data for your reference.

@Anonymous  Thanks for providing the sample data. Please try to do the same for any queries in future.

 

Here is the solution based on the test data provided.

 

PreviousMonthCount = CALCULATE(SUM('PBI - sample data'[Count of Assistants]),PREVIOUSMONTH('PBI - sample data'[FORECAST_START_WEEK_DATE]))
PrevioustoPreviousMonthCount = 
CALCULATE(SUM('PBI - sample data'[Count of Assistants]),PREVIOUSMONTH(PREVIOUSMONTH('PBI - sample data'[FORECAST_START_WEEK_DATE])))

image.png

 

Let me know if there is any further issues....





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

Hi @PattemManohar 

That worked!!! Thanks. Thank you much!!!!!

Anonymous
Not applicable

I didn't understand your solution. I've attached the sample data. please refer that.

Excel data

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.