comparing current year with last year

[Anonymous]

Hi guys,

     I am new to power bi i want to compare the current year with Last Year. I am Having data from 2017 january to 2019 November.

I have connected through Live Cube data. I have used a formula 

Current Year = CALCULATE (SUM ( 'Table1'[TotalAmount] ),FILTER (ALLSELECTED ( 'Table1' ),FORMAT ( 'Table1'[Date], "YYYY" )= FORMAT(MAX('Table1'[Date])),"YYYY"))))

Last_year = CALCULATE (SUM ( 'Table1'[TotalAmount] ),FILTER (ALLSELECTED ( 'Table1' ),FORMAT ( 'Table1'[Date], "YYYY" )= FORMAT(EDATE(MAX('Table1'[Date]),-1),"YYYY")))

But when i click the Year filter say 2018 i must compare the 2018 and 2017 values but instead the Last year value becomes blank. I have tried using SAMEPERIODLASTYEAR Function also but the same issue is obtained. please help me with it and provide me a solution.

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

Replace ALLSELECTED in the formula with ALL to see if it works, or you can try the following measure:

Current Year =
CALCULATE (
    SUM ( 'Table1'[TotalAmount] ),
    FILTER ( ALL ( 'Table1' ), Table1[Date].[Year] = MAX ( Table1[Date].[Year] ) )
)

Last_year =
CALCULATE (
    SUM ( 'Table1'[TotalAmount] ),
    FILTER (
        ALL ( 'Table1' ),
        Table1[Date].[Year]
            = MAX ( Table1[Date].[Year] ) - 1
    )
)

If it doesn't meet your requirement, kindly share your sample data if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-joesh-msft  Hi ,

                                 Thanks for your help. I have got it and could you tell me for calculating month wise i.e, i want to compare current month with previous month and last 3rd month. becasue when i use this Dax monthwise I cannot filter it year wise i.e, if i click the year filter say 2018 i am not getting the values for January 2018 to December 2018 instead i am getting the sum of all the years from january to December.

Current month =
CALCULATE (
    SUM ( 'Table1'[TotalAmount] ),
    FILTER ( ALL ( 'Table1' ), Table1[Date].[month] = MAX ( Table1[Date].[month] ) )
)

Last_month =
CALCULATE (
    SUM ( 'Table1'[TotalAmount] ),
    FILTER (
        ALL ( 'Table1' ),
        Table1[Date].[month]
            = MAX ( Table1[Date].[month] ) - 1
    )
)

 Last3Month = CALCULATE (SUM ( 'Table1'[TotalAmount] ),FILTER (ALL ( 'Table1' ),Table1[Date].[month]= MAX ( Table1[Date][month] ) - 2))

[v-joesh-msft]

Hi @Anonymous ,

You need to add a condition similar to the following:

Current month =
CALCULATE (
    SUM ( 'Table1'[TotalAmount] ),
    FILTER (
        ALL ( 'Table1' ),
        Table1[Date].[month] = MAX ( Table1[Date].[month] )
            && Table1[Date].[year] = MAX ( Table1[Date].[year] )
    )
)

Last_month =
CALCULATE (
    SUM ( 'Table1'[TotalAmount] ),
    FILTER (
        ALL ( 'Table1' ),
        Table1[Date].[month]
            = MAX ( Table1[Date].[month] ) - 1
            && Table1[Date].[year] = MAX ( Table1[Date].[year] )
    )
)

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @sathish_kumar ,

Could you post a screenshot of the model view?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.