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

[BerlinAlexander]

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?

[mwegener]

Hi @BerlinAlexander ,

was your problem solved?

[v-yiruan-msft]

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

[mahoneypat]

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

[amitchandak]

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