Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Endurion
Helper I
Helper I

Calculation with multible variables in time

‎08-11-2022 12:59 AM

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 calculate the difference. To counter very small values there is a IF statement to filter these small values. My current Dimensionula is:

 

VAR LY_This_Period = CALCULATE(SUM('Table1'[Amount]),'Table1'[Dimension]=“Dimension”)

 

VAR LY_Past_Period = CALCULATE(SUM('Table1'[Amount]),PARALLELPERIOD('Calendar'[Date],-1,MONTH),'Table1'[Dimension]=“Dimension”)

 

VAR LY_Production_Period_DuringYear = CALCULATE(SUM('Table1'[Amount]),'Table1'[Dimension]=“Dimension”)-CALCULATE(SUM('Table1'[Amount]),PARALLELPERIOD('Calendar'[Date],-1,MONTH),'Table1'[Dimension]=“Dimension”)

 

RETURN

 

IF(AND(LY_Production_Period_DuringYear<0.049,LY_Production_Period_DuringYear>-0.049),BLANK(),

    IF(OR(LY_Past_Period=0,LY_This_Period=0),BLANK(),LY_Production_Period_DuringYear))

 

But the problem that I face is that in the month Januari it should not take the parral period but the first date of the year. Consequential it is needed that in February the calculation only is made based on the last date of January instead of the total calculation of January.

 

Do you have any tips or suggestions?

Message 1 of 12
649 Views
0
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support
In response to Endurion

‎08-22-2022 03:09 AM

Hi, @Endurion 

 

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])

vzhangti_0-1661162667120.png

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.

vzhangti_3-1661162796899.png

But in your results.

vzhangti_4-1661162867459.png

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.

View solution in original post

Calculation with multible variables in time.pbix
Message 11 of 12
459 Views
0
11 REPLIES 11
Endurion
Helper I
Helper I

‎08-16-2022 11:29 PM

@v-zhangti @lbendlin , sorry for my late responce I was on holiday.

 

This is the problem. Hope it helpst, otherwise please let me know.

Endurion_0-1660717732803.png

 

Message 4 of 12
559 Views
0
lbendlin
Super User
Super User
In response to Endurion

‎08-17-2022 12:16 PM

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.

Message 5 of 12
540 Views
0
Endurion
Helper I
Helper I
In response to lbendlin

‎08-17-2022 10:39 PM

You may use the data of the picture. This is a simplified sample. I can not share other data due to the GDPR.

Message 6 of 12
534 Views
0
lbendlin
Super User
Super User
In response to Endurion

‎08-18-2022 04:11 AM

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Message 7 of 12
514 Views
0
Endurion
Helper I
Helper I
In response to lbendlin

‎08-18-2022 11:16 PM

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

 

Message 8 of 12
508 Views
0
v-zhangti
Community Support
Community Support
In response to Endurion

‎08-22-2022 03:09 AM

Hi, @Endurion 

 

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])

vzhangti_0-1661162667120.png

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.

vzhangti_3-1661162796899.png

But in your results.

vzhangti_4-1661162867459.png

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.

Calculation with multible variables in time.pbix
Message 11 of 12
460 Views
0
Endurion
Helper I
Helper I
In response to v-zhangti

‎08-22-2022 04:58 AM

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

This methode does work so thank you.

Message 12 of 12
455 Views
0
lbendlin
Super User
Super User
In response to Endurion

‎08-19-2022 06:22 AM

 

Conditional Delta := 
var a = max(Data[Date])
var b = CALCULATE(max(Data[Date]),All(Data),Data[Date]<a)
var c = CALCULATE(sum(Data[Value]),all(Data),Data[Date]=b)
var d = CALCULATE(sum(Data[Value]),all(Data),Data[Date]=a)
return d-c

 

 

see attached. You can modify as needed to cater for the dimension filters etc.

calculation with multiple variables.pbix
Message 9 of 12
493 Views
0
Endurion
Helper I
Helper I
In response to lbendlin

‎08-21-2022 11:23 PM

Hi @lbendlin , based on your file the outcomes are diffrent. 

Message 10 of 12
466 Views
0
v-zhangti
Community Support
Community Support

‎08-16-2022 10:42 PM

Hi, @Endurion 

 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

 

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.

Message 3 of 12
564 Views
0
lbendlin
Super User
Super User

‎08-13-2022 07:22 PM

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Message 2 of 12
603 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All