cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
valentina14 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: Getting data of previous month.

@valentina14  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 Datanaut !





View solution in original post

5 REPLIES 5
Super User
Super User

Re: Getting data of previous month.

@valentina14 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 Datanaut !





valentina14 Frequent Visitor
Frequent Visitor

Re: Getting data of previous month.

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

Excel data

 

 

valentina14 Frequent Visitor
Frequent Visitor

Re: Getting data of previous month.

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.

Super User
Super User

Re: Getting data of previous month.

@valentina14  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 Datanaut !





View solution in original post

valentina14 Frequent Visitor
Frequent Visitor

Re: Getting data of previous month.

Hi @PattemManohar 

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 153 members 1,651 guests
Please welcome our newest community members: