Calculate Total amount less than selected value from Slicer

[Ghoghnuse]

I have a year slicer (2017, 2018, ....) and a Month Number slicer (1, 2,....) and multiple Data Tables with year and month Columns.

I want to filter all tables by one Month and year slicer so I Created a year and a month table and connect Them to Data tables.

When I filter my data using Slicers created from year and month tables everything is ok. but I want a measure that Calculates the total amount from the beginning of the year to the selected Month from Slicer.

My old measure ( that calculate total month amount) was :

Prepayment = CALCULATE(sum(Balance[Amount]),
Balance[Type 2]="Local",
Balance[Type 1]="Prepayment")

Created a new measure to calculate total amount from month 1 to selected slicer month in selected year:

Prepayment = CALCULATE(sum(Balance[Amount]),
Filter(Balance,
Balance[TRANS_Month]<=SELECTEDVALUE('Month'[MonthN]) &&
Balance[TRANS_Year]=SELECTEDVALUE('Year'[YearN]) &&
Balance[Type 2]="Local" &&
Balance[Type 1]="Prepayment"))

But Measure calculates last month's Total, not the selected month.

Any solution for the easiest way is greatly appreciated.

Kind Regards.

[v-angzheng-msft · Accepted solution]

Hi, @Ghoghnuse 

The logic of the above formula is undoubtedly correct. But you missed one thing, when the filter is selected, it will show the filtered data. So you can't get the correct YTD.

Use the All function to return all the data in the table.

Modify your formula as follows:

Prepayment =
CALCULATE(
    SUM( Balance[Amount] ),
    FILTER(
        ALL( 'Balance' ),
        Balance[TRANS_Month] <= SELECTEDVALUE( 'Month'[MonthN] )
            && Balance[TRANS_Year] = SELECTEDVALUE( 'Year'[YearN] )
            && Balance[Type 2] = "Local"
            && Balance[Type 1] = "Prepayment"
    )
)

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[amitchandak]

@Ghoghnuse , This should give you YTD, what you have , assuming the month and year are independent table

Prepayment = CALCULATE(sum(Balance[Amount]),
Filter(Balance,
Balance[TRANS_Month]<=SELECTEDVALUE('Month'[MonthN]) &&
Balance[TRANS_Year]=SELECTEDVALUE('Year'[YearN]) &&
Balance[Type 2]="Local" &&
Balance[Type 1]="Prepayment"))

Current month

Prepayment = CALCULATE(sum(Balance[Amount]),
Filter(Balance,
Balance[TRANS_Month]=SELECTEDVALUE('Month'[MonthN]) &&
Balance[TRANS_Year]=SELECTEDVALUE('Year'[YearN]) &&
Balance[Type 2]="Local" &&
Balance[Type 1]="Prepayment"))

My advice would be to create a date using month and year and then join with date table 

Date = Date(Balance[TRANS_Year], Balance[TRANS_Month],1)

Join with date table

Then use time intelligence

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

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

[Ghoghnuse]

@amitchandak

Thanks a lot . But measure show total amount and slicer does not affect calculation .

I created date column as you said . how use slicer value in YTM function or mybe i had to use another function?

this measure show total value the same as another one :

YTDPre = TOTALYTD([Prepayment],Balance[DATE],

Balance[DATE].[MonthNo]<=SELECTEDVALUE('Slicer Column]'))

Thanks in advance .