Forum Discussion

Sania-F's avatar
Sania-F
Resolver I
2 years ago

DAX modification help needed

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

                )

            )

        )

    )
 

 

DAX

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

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