Forum Discussion

Thomas94401's avatar
Thomas94401
Icon for Helper I rankHelper I
4 years ago
Solved

Convert Month to string like M1 depending on the filter

Hi, 

 

I have a Sales table by month: 

DateOptionSales
Jan-2019A1
Jan-2019B2
Feb-2019A3
Feb-2019B4
Mars-2019A5
Mars-2019B6

 

I want to build a groupby table summing sales. For example, an output I would like : 

If I filter on Date between Feb2019 and June2019 : I would like : 

DateSum  of sales
M17
M211

 

with :

- M1 = first month of the filter

- M2 = second month of the filter ...

 

Do you know if it is possible to build this column ? 

 

Thanks in advance, 

 

I tried everything and cannot obtain this result 😞

        

Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    I did it on an existing model, so you can adapt same approach.  I made the disconnected table called MonthTable.

     

     

    And my Date table already had a column for the MonthEnding, so I used that in this measure to get the result shown.

     

    SlicerMonth =
    VAR slicermon =
        MIN ( MonthTable[MonthsAway] )
    VAR EOMminslicerdate =
        EOMONTH ( MIN ( 'Date'[Date] ), slicermon )
    RETURN
        CALCULATE ( [Total Sales], 'Date'[MonthEnding] = EOMminslicerdate )

     

     

     

    Pat

     

     

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    To do that, you need to create a disconnected table like the one below, and then in a variable in your measure calculate the month that would be that many MonthsFromSelection from the min date from the slicer.  You would then return your measure (sum in your case) for that month.

     

    Month

    MonthsFromSelection
    M1 0
    M2 1
    M3 2

     

    Pat

    • Thomas94401's avatar
      Thomas94401
      Icon for Helper I rankHelper I
      4 years ago

       

      Hi, Thanks a lot for your help ! 

       

      I created this PBIX and the disconnected table. I was stuck on how you create the variable MonthFromSelection ?




       

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        4 years ago

        I did it on an existing model, so you can adapt same approach.  I made the disconnected table called MonthTable.

         

         

        And my Date table already had a column for the MonthEnding, so I used that in this measure to get the result shown.

         

        SlicerMonth =
        VAR slicermon =
            MIN ( MonthTable[MonthsAway] )
        VAR EOMminslicerdate =
            EOMONTH ( MIN ( 'Date'[Date] ), slicermon )
        RETURN
            CALCULATE ( [Total Sales], 'Date'[MonthEnding] = EOMminslicerdate )

         

         

         

        Pat

         

         

  • Thomas94401's avatar
    Thomas94401
    Icon for Helper I rankHelper I
    4 years ago

    Hi, Thanks a lot for your help ! 

     

    I created this PBIX and the disconnected table. I was stuck on how you create the variable MonthFromSelection ?