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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors