Forum Discussion

ngomes's avatar
ngomes
Helper II
4 years ago
Solved

Data segmentation

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...
Need Help
  • ngomes's avatar
    ngomes
    4 years ago

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

ngomes's avatar
ngomes
Helper II
4 years ago

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's avatar
v-easonf-msft
Community Support
4 years ago

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's avatar
    ngomes
    Helper II
    4 years ago

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