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

[AdisanonW]

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 🙂

[v-yangliu-msft · Accepted solution]

Hi  @AdisanonW ,

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

[AdisanonW]

Wow! it works. Thank you so much 😄 @v-yangliu-msft 

[AdisanonW]

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

[lbendlin]

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

[AdisanonW]

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 🙂

[lbendlin]

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