Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculation with multible variables in time

Hi all,   I am facing a problem in my Power BI File.   I am making a calculation based on some variable. What I am doing is comparing two numbers (current and past period) with each other to calc...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    Based on the data you provide, you can try the following methods.

    Current value = SUM(Data[Value])
    Previous value = 
Var PrevDate=MAXX(FILTER(ALL('Data'[Date],Data[Year]),'Data'[Date]<SELECTEDVALUE('Data'[Date])&&[Year]=SELECTEDVALUE(Data[Year])),'Data'[Date])
Var Prevalue=CALCULATE([Current value],FILTER(ALL(Data),[Date]=PrevDate))
Return
Prevalue
    Outcome = IF([Previous value]<>BLANK(), [Current value]-[Previous value])

    The result of the calculation is different from the result you expect. Consider if there is a problem with your desired result calculation?

     

    Let me give you an example.

    But in your results.

    Please check if there is a problem with your result calculation in Excel, thank you.

     

    Best Regards,

    Community Support Team _Charlotte

    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
4 years ago

Herby simplified data.

 

Data

DateValueDimension
1-1-2021100LY
1-1-2021100LY
1-1-2021125LY
1-1-2021100LY
30-1-2021125LY
30-1-2021125LY
30-1-2021150LY
30-1-2021125LY
28-2-2021200LY
28-2-2021200LY
28-2-2021200LY
28-2-2021200LY
30-3-2021300LY
30-3-2021300LY
30-3-2021300LY
30-3-2021300LY
30-4-2021500LY
30-4-2021500LY
30-4-2021500LY
30-4-2021500LY
30-5-2021600LY
30-5-2021600LY
30-5-2021600LY
30-5-2021600LY
30-6-20211000LY
30-6-20211000LY
30-6-20211000LY
30-6-20211000LY
30-7-20211000LY
30-7-20211000LY
30-7-20211000LY
30-7-20211000LY
30-8-20211300LY
30-8-20211200LY
30-8-20211300LY
30-8-20211400LY
30-9-20211500LY
30-9-20211500LY
30-9-20211500LY
30-9-20211500LY
30-10-20212500LY
30-10-20212500LY
30-10-20213000LY
30-10-20212000LY
30-11-20215000LY
30-11-20215000LY
30-11-20215000LY
30-11-20215000LY
30-12-20216500LY
30-12-20216500LY
30-12-20217000LY
30-12-20216000LY
1-1-2022500LY
1-1-2022500LY
1-1-2022500LY
1-1-2022500LY
30-1-2022750LY
30-1-2022500LY
30-1-20221000LY
30-1-2022750LY
28-2-20221000LY
28-2-20221000LY
28-2-20221000LY
28-2-20221000LY
30-3-20222000LY
30-3-20222000LY
30-3-20222000LY
30-3-20222000LY
30-4-20222500LY
30-4-20222500LY
30-4-20222500LY
30-4-20222500LY
30-5-20223000LY
30-5-20223000LY
30-5-20223000LY
30-5-20223000LY
30-6-20226500LY
30-6-20223000LY
30-6-20223000LY
30-6-20223000LY
30-7-20225000LY
30-7-20226500LY
30-7-20223000LY
30-7-20223000LY
30-8-20229000LY
30-8-20226500LY
30-8-20223000LY
30-8-20223000LY
30-9-202210000LY
30-9-20226500LY
30-9-20223000LY
30-9-20223000LY
30-10-202210000LY
30-10-202210000LY
30-10-20223000LY
30-10-20223000LY
30-11-202210000LY
30-11-202210000LY
30-11-20225000LY
30-11-20223000LY
30-12-202210000LY
30-12-202210000LY
30-12-202210000LY
30-12-202210000LY

 

Outcome needed

YearMonthValue
2021Januari100
2021Februari275
2021Maart400
2021April800
2021Mei275
2021Juni400
2021Juli800
2021Augustus400
2021September400
2021Oktober800
2021November400
2021December1600
2022Januari800
2022Februari400
2022Maart1600
2022April0
2022Mei400
2022Juni1600
2022Juli0
2022Augustus1200
2022September1600
2022Oktober0
2022November1200
2022December800

 

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
4 years ago

Hi, Anonymous 

 

Based on the data you provide, you can try the following methods.

Current value = SUM(Data[Value])
Previous value = 
Var PrevDate=MAXX(FILTER(ALL('Data'[Date],Data[Year]),'Data'[Date]<SELECTEDVALUE('Data'[Date])&&[Year]=SELECTEDVALUE(Data[Year])),'Data'[Date])
Var Prevalue=CALCULATE([Current value],FILTER(ALL(Data),[Date]=PrevDate))
Return
Prevalue
Outcome = IF([Previous value]<>BLANK(), [Current value]-[Previous value])

The result of the calculation is different from the result you expect. Consider if there is a problem with your desired result calculation?

 

Let me give you an example.

But in your results.

Please check if there is a problem with your result calculation in Excel, thank you.

 

Best Regards,

Community Support Team _Charlotte

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
    4 years ago

    v-zhangti my bad. Indeed my demo data calculation was wrong, my apologies.

    This methode does work so thank you.