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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors