Forum Discussion

kitala11's avatar
kitala11
Helper III
5 years ago
Solved

Calculation using Filters and Group By

I have these 2 columns Current Month and Next Month. 

 

 

Eg. in this case I have October Next month (prediction) value of 1,692.86. And I want to use the Current Month of November 1317.91 (actual value) and calculate an accuracy value of (1692.86-1317.91)/1317.91. 
(Likewise do so for other consecutive months if possible)

Creating a new column MMM-YYYY allowed me to use the following to do aggregated calculations in month

 

A column in date/month  table 
Month Rank = RANKX(all('Date'),'Date'[year Month],,ASC,Dense)

 

measures
MTD current month = CALCULATE(SUM(Table[Current Month]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
last MTD next month = CALCULATE(SUM(Table[Next Month]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

 

And what I wanted to calculate for the whole month, However, now I want to break it down in category (probably group by the categories for calculation) is there anyway to do so?

 

 

 
 
 
 
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi kitala11 ,

     

    First, create a calculated column to get the month number.

    MonthNo = SWITCH([version], 
        "Jan", 1,
        "Feb",2,
        "Mar",3,
        "Apr",4,
        "May",5,
        "Jun",6,
        "Jul",7, 
        "Aug",8,
        "Sep",9,
        "Oct",10,
        "Nov",11,
        "Dec",12
                )

     

    Then try this measure. The tt is to get the the Current Month of November 1317.91 (actual value). The dd is to get the Next month (prediction) value of 1,692.86. The result should be 0.22.

    accuracy value =
VAR tt =
    CALCULATE (
        SUM ( 'Table'[Current Month] ),
        FILTER ( ALL ( 'Table' ), [MonthNo] = MAX ( 'Table'[MonthNo] ) + 1 )
    )
VAR dd =
    SUM ( 'Table'[Next Month] )
RETURN
     ( dd - tt ) / dd

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • kitala11's avatar
    kitala11
    Helper III
    5 years ago

    Example in such a way (right now aggregation is for the whole month so the values in PREV MOnth are all identical).

     

     

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      kitala11 , hope you are using month year from a separate table, not using rank in the same table.

      Month year and month year rank booth should be used from a separate table. Also in visual month year should come from the this separate table .

       

      Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

       

      • kitala11's avatar
        kitala11
        Helper III
        5 years ago

        Okay, but can you explain to me why the month year and month rank table should be separate of the main data table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi kitala11 ,

     

    First, create a calculated column to get the month number.

    MonthNo = SWITCH([version], 
        "Jan", 1,
        "Feb",2,
        "Mar",3,
        "Apr",4,
        "May",5,
        "Jun",6,
        "Jul",7, 
        "Aug",8,
        "Sep",9,
        "Oct",10,
        "Nov",11,
        "Dec",12
                )

     

    Then try this measure. The tt is to get the the Current Month of November 1317.91 (actual value). The dd is to get the Next month (prediction) value of 1,692.86. The result should be 0.22.

    accuracy value =
VAR tt =
    CALCULATE (
        SUM ( 'Table'[Current Month] ),
        FILTER ( ALL ( 'Table' ), [MonthNo] = MAX ( 'Table'[MonthNo] ) + 1 )
    )
VAR dd =
    SUM ( 'Table'[Next Month] )
RETURN
     ( dd - tt ) / dd

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi kitala11 ,

    Could you tell me if your problem has been solved?
    If it is, kindly Accept it as the solution. More people will benefit from it.
    Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


    Best Regards,
    Stephen Tao