Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Aymen_Soussi
Frequent Visitor

Accumulated Formula between 2 years

Hello I'm new to dax syntaxe and I have an issue, I want to create a mesure that calculate the accumulated value between 2 years 2021 and 2022, that's mean in 2021 it get the normal budget value but in 2022 it get the sum of the budget for 2021 and 2022.

Sans titre.png 

2 REPLIES 2
selimovd
Super User
Super User

Hey @Aymen_Soussi ,

 

try the following approach:

Accumulated =
VAR vCurrentYear = YEAR( MAX( Abfrage1[date] ) )
RETURN
    IF(
        vCurrentYear = 2022,
        CALCULATE(
            SUM( Abfrage1[budget] ),
            DATESINPERIOD(
                'Date'[Date],
                MAX( 'Date'[Date] ),
                -1,
                YEAR
            )
        ),
        SUM( Abfrage1[budget] )
    )

 

Be aware that for that approach and for many that use time intelligence you need a date table:

Power Bi for Beginners: How to create a Date Table in Power Bi - Softcrylic

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

First of all thank you very much @selimovd  for the quick response.

I just did some changes like  (''Abfrage1'[date] in state of Date['Date']) I also changed SUM( Abfrage1[budget] ) by MAX( Abfrage1[budget] ) in the end of the query and the result is fine for the year 2021, but for the year 2022 it's not working like I want, may be I haven't clarify enough but the sum must be like this exemple : date :01/01/2021 budget :200, date :01/01/2022 budget :300, -> accumulated mesure for 2021 :200 which is ok by our work , accumulated mesure for 2022 : 500 !! so the sum must be like this:  01/01/2021 with 01/01/2022, then 01/02/2021 with 01/02/2022 ... by the acctual formula I think it calculate the sum of the hole year but what I want is to calculate the sum between the same dates ex: (01/01/2021 and 01/01/2022) but between the 2 years. I hope you get the idea @selimovd and thanks.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors