cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

SAMEPERIODLASTYEAR and PREVIOUSMONTH in the same DAX based on slicer? [pbix attached]

Hi Guys,

 

Just started learning Powerbi to create reports for my organization. I have been studying from Avi Singh's videos.

 

I have attached a sample pbix that matches my actual data. I am trying to find a way to see if the below is possible :

 

There are two slicers, one for a month and one for year. I want to see if there can be a DAX that will show me the previous month's value if a particular month is selected from slicer. And if no month is selected, then show me the value of the previous year based on the year slicer.

 

 

Is that possible guys? Here is my power bi file : pbix file

1 ACCEPTED SOLUTION

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Solution Sage
Solution Sage

Hi - Please try below. Added one condiiton forr Both condition. You can add to Switch if there is any specific requirement for that. Below will check for Month first and then for Year. Manipulate Switch as per requirement.

Calc_Measure = 
var MonthSelected = not(isblank(SELECTEDVALUE('Date'[Month])))
var YearSelected = not(isblank(SELECTEDVALUE('Date'[Year])))
-- var BothSelected = MonthSelected && YearSelected

return 
SWITCH(True(),MonthSelected,[Value preious month1],YearSelected,[Value previous year],BLANK())

Thanks
Ankit Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

Hey @AnkitBI ,

 

Your response was really helpful! It did solve my problem, but i ran into another one. Can you see if you can help me solve it?

 

With your dax in my model, if the year selected is 2019, the data gets shown for 12/31/2018, but that isnt really sameperiodlastyear if you think about it. 2019's data ends at 8/31/2019. So shouldnt i see data for 8/31/2018?

 

I want the model to be dynamic. So if the dataset refreshes at the end of this month and i have data for 9/30/2019. Then it should show 9/30/2018 and so on. Hope you get what i am trying to say.

Ok. I didn't went into these details. I thought you already have done these and looking for how to switch.

Ashish solutions mostly works :). Do check as he has replied on it.

Thank you Ankit.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

Thanks Ashish. Your solution mostly worked! I just needed to tweak it a bit to get the right value. Since you had the today() value entered for EOMONTH, it was showing 12 months back data based on today's date but i needed 12 months back data based on the last month of data of 2019.

 

So i did a minor tweak :

Previous value 2 = if(HASONEVALUE('Date'[Month]),CALCULATE([Total value],PREVIOUSMONTH('Date'[Date])),CALCULATE([Total value],DATESBETWEEN('Date'[Date],EDATE(EOMONTH(LASTDATE(Table1[Report Date]),0),-12),EDATE(EOMONTH(LASTDATE(Table1[Report Date]),0),-12))))

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors