DAX modification help needed

[Sania-F]

HI All,

Please help in getting expected data.

I have  date table with columns [isEndOfQuarter] with values yes, no and [isCurrentFiscalYear] with values yes, no.

I have a time-period table with column [mon-fyyear] with vaues as sep-fy24,oct-fy24,nov-fy24,dec-fy24,jan-fy24,feb-fy24 and so on

I have a factEMT table with column [rev pln], factunit table, factgroup table with column [value]

For last fiscal year , I should show sum(factemt[revplan])

Whenever the pbi page scope is in selected unit and selected group
 if the select the mon-yyyear is -> isCurrentFiscalYear = yes  and [isendofquarter]= yes and, then I should see quarter related totals

 if the select the mon-yyyear is -> isCurrentFiscalYear = yes  and [isendofquarter]= No and, then I should see fiscalyear related totals.

Data comes fine when I see EOQ total. The moment I try to see FYtotal , I see only Fytotal across all units and group

Data expected at Fytotal :

MarketUnit	ClientGroup	Plan M$
M1	CG1	100$
M1	cg2	500$
M1	cg3	700$
M1	cg4	300$
M1	cg5	1000$
M1 totsl		2600$
M2	CG1	900$
M2	cg2	200$
M2	cg3	550$
M2	cg4	130$
M2	cg5	100$
m2 total		1880$
TOTAL		4480

Data that I am getting as fytotal :

MarketUnit	ClientGroup	Plan M$
M1	CG1	4480
M1	cg2	4480
M1	cg3	4480
M1	cg4	4480
M1	cg5	4480
		4480
M2	CG1	4480
M2	cg2	4480
M2	cg3	4480
M2	cg4	4480
M2	cg5	4480
		4480
TOTAL		4480

DAX written –

rev pln sf2 =

VAR p = SELECTEDVALUE(time-period[Month-year])

VAR EOQtotal = CALCULATE(SUM(factemt[Value]), 'FactEMEAPlanTotal'[Metrics] = "REV", dimDate[isendofquarter] = "Yes")

VAR EOQunittotal = CALCULATE(SUM(factunit[Value]), factunit [Metrics] = "REV", DimDate[isendofquarter] = "Yes")

VAR EOQgrouptotal = CALCULATE(SUM('Factgroup'[Value]), ''Factgroup' [Metrics] = "REV", DimDate[isendofquarter] = "Yes")

var fytotal= CALCULATE(SUM(factemt [Revenue Plan ($)])/1000, factemt [LoadDateKey]=MAX(factemt [LoadDateKey]))

RETURN

    SWITCH (

        TRUE(),

       p IN { "Oct-FY23",so on………, "Aug-FY23" },

        CALCULATE(SUM(FactEMT[RevPln ($)]) / 1000, FactEMt[DateKey] = 20221130),

        // If DimDate[currentfiscalyear] is "Yes" and DimDate[FlagEndOfFisFiscalQuarter] is "No", return FYtotal

        IF (

            SELECTEDVALUE(DimDate[iscurrentfiscalyear]) = "Yes" &&

            SELECTEDVALUE(DimDate[isendofquarter]) = "No",

            FYtottal,

            // If the above condition is not met, return EOQtotal, EOQmutotal, or EOQmucg based on other conditions

            IF (

                ISINSCOPE(Dunit[unit]) && ISINSCOPE(DimGroup[Group]),

                EOQgrouptotal,

                IF (

                    HASONEFILTER(Dunit[Unit]),

                    EOQunittotal,

                    EOQtotal

                )

            )

        )

    )
 

[Sahir_Maharaj]

Hello @Sania-F,

Can you please try this streamlined version of your measure:

Revised Rev Plan SF2 = 
VAR IsCurrentFY = SELECTEDVALUE(DimDate[iscurrentfiscalyear]) = "Yes"
VAR IsEndOfQuarter = SELECTEDVALUE(DimDate[isendofquarter]) = "Yes"
VAR LatestLoadDate = MAX(factemt[LoadDateKey])
RETURN
    IF (
        IsEndOfQuarter,
        SUM(factemt[Value]),
        IF (
            IsCurrentFY,
            CALCULATE(
                SUM(factemt[Revenue Plan ($)]) / 1000,
                ALLSELECTED(DimDate),
                DimDate[iscurrentfiscalyear] = "Yes",
                DimDate[LoadDateKey] = LatestLoadDate
            ),
            BLANK()
        )
    )