topic Re: How to add another Calculation for only fourth Quarter in this year in DAX Commands and Tips

How to add another Calculation for only fourth Quarter in this year

Anonymous — Thu, 11 Aug 2022 11:15:09 GMT

I have 2 date tables,
1 for filtering and 2 for chart displaying as per below.

and as you can see the blank space in 2022 Q4, I need to calculate separately for only Q4. (forecast figure)

Below are the measures I have tried, but I struck on combining the values (18.1) with the main measure. (Net sales)

The conditional for calculating Q4 is: Selected month of [Actual&Forecast] - (Q1 + Q2 + Q3)

Here is my DAX formula for Net sales (Main measure):

Net sales = var var_EndOfYear = ENDOFYEAR(dDate[Date]) var LYtoTY = DATESINPERIOD(dDate_M[PeriodID],var_EndOfYear,-2,YEAR) Var Estimation = [SelectedEstimation] var ActualTY = CALCULATE( [ActualTY], REMOVEFILTERS(dDate), KEEPFILTERS(LYtoTY), USERELATIONSHIP(dDate[Date],dDate_M[PeriodID]), FILTER(dDate_m,dDate_m[Month] <= [selectedmonth] && dDate_m[Year] IN {[SelectedYear]}) ) var ActualLY = CALCULATE( REMOVEFILTERS(dDate), KEEPFILTERS(LYtoTY), USERELATIONSHIP(dDate[Date],dDate_M[PeriodID]), FILTER(dDate_m,dDate_m[Year] IN {[SelectedYear] -1}) ) var ForecastTY = CALCULATE( [ForecastTY], REMOVEFILTERS(dDate), KEEPFILTERS(LYtoTY), USERELATIONSHIP(dDate[Date],dDate_M[PeriodID]), FILTER(dDate_m,dDate_m[Month] > [SelectedMonth] && dDate_m[Year] IN { [SelectedYear]} && dDate_M[Quarter] <> 4) ) var sumActual = ActualTY + ActualLY + ForecastTY return sumActual + "I have created another measure to combine here, but failed :("

And here is Q4 DAX formula:

Net sales Q4 = var Estimation = [SelectedEstimation] var Q123 = CALCULATE( [Actual], FILTER(all(ddate),dDate[Month] <= SELECTEDVALUE(dDate[Month]) && dDate[Year] = SELECTEDVALUE(dDate[Year]) && dDate[Quarter No.] <> 4) ) + CALCULATE( [Forecast], FILTER(all(ddate),dDate[Month] > SELECTEDVALUE(dDate[Month]) && dDate[Year] = SELECTEDVALUE(dDate[Year]) && dDate[Quarter No.] <> 4) ) var FY = CALCULATE( [FYActual], FILTER(all(ddate),dDate[Month] < SELECTEDVALUE(dDate[Month]) && dDate[Year] = SELECTEDVALUE(dDate[Year])) ) + CALCULATE( [FYForecast], FILTER(all(ddate),dDate[Month] >= SELECTEDVALUE(dDate[Month]) && dDate[Year] = SELECTEDVALUE(dDate[Year])) ) var BarQ4 = FY - Q123 return BarQ4

Some suggestions?

Thanks 🙂

Re: How to add another Calculation for only fourth Quarter in this year

lbendlin — Sat, 13 Aug 2022 21:56:55 GMT

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-p/963216
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How to add another Calculation for only fourth Quarter in this year

Anonymous — Mon, 15 Aug 2022 05:25:33 GMT

My bad and thanks for your reply and suggestions.

Here is the expected result in Q4 2022 (referring to the screenshot I attached) :

Year	Quarter	Net sales
2021	Q1	15.0
2021	Q2	18.2
2021	Q3	18.6
2021	Q4	26.9
2022	Q1	23.9
2022	Q2	24.2
2022	Q3	22.2
2022	Q4	18.1

And here is the sample data (fact table) :

Currency	Value	ReportElement	DataLevel	OrganisationElementName	DateKey
EUR	2051.42	A	Actual	T	01-Jul-22
EUR	-39.56	B	Actual	E	01-Jun-22
EUR	-221727.6	C	Actual	E	01-Jul-22
EUR	-5893821.02	D	Actual	E	01-Jul-22
EUR	676001.66	E	Actual	E	01-Jul-22
EUR	981124.04	F	Actual	T	01-Jul-22
EUR	3029083	G	Estimation	T	01-Dec-22

Re: How to add another Calculation for only fourth Quarter in this year

lbendlin — Mon, 15 Aug 2022 17:18:39 GMT

I don't think your sample data matches your expected output. Please verify, and also provide the forecast table.

Re: How to add another Calculation for only fourth Quarter in this year

Anonymous — Tue, 16 Aug 2022 03:22:43 GMT

I understand your point now.

Let me give you more context via this link (Excel), it is included the issue explanation and covered of sample data.

Thank you so much 🙂

Re: How to add another Calculation for only fourth Quarter in this year

Anonymous — Wed, 17 Aug 2022 06:09:15 GMT

Hi Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Net sales = 1

Net sales Q4 = 2

Flag1 = SWITCH( TRUE(), MAX('Table'[Year])=2022&&MAX('Table'[QTR])="Q4",[Net sales Q4], HASONEVALUE('Table'[Year])&&HASONEVALUE('Table'[QTR]),[Net sales])

Flag2 = var _table= SUMMARIZE('Table','Table'[QTR],"value",[Flag1]) return IF( HASONEVALUE('Table'[QTR]),[Flag1],SUMX(_table,[value]))

all = var _table= SUMMARIZE('Table','Table'[Year],"value",[Flag2]) return IF( HASONEVALUE('Table'[Year]),[Flag2],SUMX(_table,[value]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: How to add another Calculation for only fourth Quarter in this year

Anonymous — Wed, 17 Aug 2022 08:41:25 GMT

Wow! it works. Thank you so much 😄 Anonymous