Calculation using Filters and Group By

[kitala11]

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?

[v-stephen-msft]

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

[v-stephen-msft · Accepted solution]

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.

[kitala11]

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

[amitchandak]

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

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

[amitchandak]

@kitala11 , When you use separate table or date table

MTD current month = CALCULATE(SUM(Table[Current Month]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))

when you use all or allselected on the table. means you now need to build the context using the values of the row. example

MTD current month = CALCULATE(SUM(Table[Current Month]), FILTER(ALL('Table'),'Table'[Month Rank]=max('Table'[Month Rank]) && 'Table'[Category]=max('Table'[Category])))

LMTD current month = CALCULATE(SUM(Table[Current Month]), FILTER(ALL('Table'),'Table'[Month Rank]=max('Table'[Month Rank]) -1 && 'Table'[Category]=max('Table'[Category])))