Data segmentation

[ngomes]

Good morning everyone,

I need to compare the last month with the previous one but I would like to use a data segmentation with the months and I would like to know what the last month is, getting the dynamic dax formula

thanks for any help you can give me

[amitchandak]

@ngomes , Not very clear. A various methods to get this month vs last month

MTD = CALCULATE(AverageX(values('Date'[Date]), calculate(SUM(Table[Qunatity Produced])) ),DATESMTD('Date'[Date]))

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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[ngomes]

sorry I didn't explain myself well

I have a scroller where I have the values of the month and the deviation from the previous month

the formulas I currently have are
MarginMonth = if (MONTH(TODAY())= Sales[Month], Sales[Margin],0)
and
MarginMonth-1 = if (MONTH(TODAY())-1= Sales[Month], Sales[Margin],0)

which causes the formulas to be stuck to the current and previous month

what I wanted was for the formulas to be dynamic according to the selection made in the data segmentation made by the user

I hope I was a little clearer now

[v-easonf-msft]

Hi, @ngomes 

If I understand correctly, you can try to add a seperated calendar table, then try formula as below:

MarginMonth =
VAR _today =
    SELECTEDVALUE ( 'calendar1'[Date] )
RETURN
    IF ( MONTH ( _today ) = Sales[Month], Sales[Margin], 0 )

MarginMonth =
VAR _today =
    SELECTEDVALUE ( 'calendar1'[Date] )
RETURN
    IF ( MONTH ( _today )-1 = Sales[Month], Sales[Margin], 0 )

Best Regards,
Community Support Team _ Eason

[ngomes · Accepted solution]

Thank you for your help

I ended up solving it with the formula:

FILTER ( ALL ( Sales[Year] ), Year_1 = Sales[Year] )

in the end it looked like:

VAR MonthYear =
Sales[YearselectedMonth2]
VAR Year_1 =
Sales[Selected Year 2-1]
RETURN

CALCULATE (
Sum(Sales[Margin]) ,
FILTER ( ALL ( Sales[Year] ), Year_1 = Sales[Year] )
)