Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

comparing current year with last year

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 for...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    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.

v-joesh-msft's avatar
v-joesh-msft
Icon for Solution Sage rankSolution Sage
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-joesh-msft
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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.