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 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.

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.

4 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    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.

  • 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.