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

Calculate something in 2 different ways depending on the value in a column

In my dashboard I have a slicer, in which you can select the month of the analysis.

 

I want to make PowerBI calculate a field in the dashboard one way if the current month is selected in the slicer and a different way if the month selected in the slicer is a past month.

 

I store dates in the column 'Date'[Date].

 

I have difficulties figuring it out in DAX. My efforts went in the direction:

 

Test = IF(
(MONTH('Date'[Date]) < MONTH(TODAY())),
(action for past months), (action for the present month)
)
 
It doesn't work.
 
I guess I need some kind of formula that will mean "for values ... do this and that" and not an IF statement. Could you please advise?
4 REPLIES 4
mwegener
Super User II
Super User II

Hi @BerlinAlexander ,

 

was your problem solved?

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


yingyinr
Community Support
Community Support

Hi @BerlinAlexander ,

Could you please provide some sample data and the expected result in order to provide you the solution suitable for your scenario? And if it involves any other function in your requirement, please provide the calculation logic and more details on it. Thank you.

In addition, you can also refer the solutions in the following threads.

Current Month , Previous Month and Before Previous Month

Create a measure that shows me the previous month

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mahoneypat
Super User IV
Super User IV

You need to aggregate your Date column first.  Please try this small change

 

Test = IF(MIN('Date'[Date])) < MONTH(TODAY())),
(action for past months), (action for the present month)
)
 
Also, watch out if your data has data before 2020.  If so, you'll need to compare year and month (or a yearmonth integer column).
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User IV
Super User IV

@BerlinAlexander , No clear. You use MTD to get this month vs last with date calendar.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

 

refer : https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors