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 dynamic dax formula

 

thanks for any help you can give me

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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'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