cancel
Showing results for
Did you mean:
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
Super User II

Please mark my post as solution, this will also help others.

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."

Community Support

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.
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!

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))
``````

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://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...

Proud to be a Super User!

Announcements