Forum Discussion

Harry_Tran's avatar
Harry_Tran
Helper III
5 years ago
Solved

Prior n month

Hello eveyone,

I have a table like this

I have a measure:

>20% = divide (

                            sum(table[>20]) + sum(table[>30]) + sum(table[>40])

                            ,

                            sum(table[Total]),0

                          )

I create a Month column in SQL with the query:

 Month = case when Dateorder = 1 then 'Current Month'

                         when Dateorder in (2,3,4,5,6,7) then 'Prior 6 Month'

                          else 'Else"

I have a matrix like this in Power BI

 

Now what I am trying to do is create a matrix with Prior Month and Prior 3 Month.

So If DateOrder = 2, it will be 'Prior Month'

     If DateOrder in (2,3,4), it will be 'Prior 3 Month'

     If Dateorder in (2,3,4,5,6,7), it will be 'Prior 6 Month'

I do not know how to group the month together like this.

If anyone know how or did work on the same situation before, please help!

Thank you so much

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Harry_Tran ,

     

    Create a dimension table as below:

    Then create a measure as below:

    Measure = IF(NOT(ISFILTERED('Slicer table'[Category])),1,SWITCH(SELECTEDVALUE('Slicer table'[Category]),
"Prior Month",IF(MAX('Table'[Dateorder])=2,1,BLANK()),
"Prior 3 Month",IF(MAX('Table'[Dateorder]) in {2,3,4},1,BLANK()),
"Prior 6 Month",IF(MAX('Table'[Dateorder]) in {2,3,4,5,6,7},1,BLANK())))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Harry_Tran 

     

    So you can't use the Month column as you have 'Prior 3 Month' and 'Prior 6 Month', when you calculate your measure, can you try like this?

    Prior 3 Month = CALCULATE(yourMeasure, Filter(yourTable, yourTable[ DateOrder] in (2,3,4))

Prior 6 Month = CALCULATE(yourMeasure, Filter(yourTable, yourTable[ DateOrder] in (2,3,4,5,6,7))

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi Harry_Tran ,

     

    Create a dimension table as below:

    Then create a measure as below:

    Measure = IF(NOT(ISFILTERED('Slicer table'[Category])),1,SWITCH(SELECTEDVALUE('Slicer table'[Category]),
"Prior Month",IF(MAX('Table'[Dateorder])=2,1,BLANK()),
"Prior 3 Month",IF(MAX('Table'[Dateorder]) in {2,3,4},1,BLANK()),
"Prior 6 Month",IF(MAX('Table'[Dateorder]) in {2,3,4,5,6,7},1,BLANK())))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!