Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic Month1 and 2 calculations

Hi All,   I'm stuck on a calculated measure I'm trying to create and would need help here. Problem Breakdown:  On the last day of the quarter/Last day of the month, we need to ensure that Month...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    v-csrikanth Thanks for trying but I managed to achieve the desired result myself using the below approach.

    The measure you shared wont work because of the flag condition. Suppose I want Month1 last day's data to be seen when I'm using the flah "Is_Last_Day_of_Fiscal_Quarter" but since that day doesn't fall under the last fiscal month of quarter the measure you tried wont work.

    I created a seperate flag table using the below DAX and altered my measure to use this flag table. 

    Let me know in case any further help/clarification is needed

    Flag_Table = DATATABLE("Flag", BOOLEAN, { { TRUE }, { FALSE } })



    This way I'm able to achieve the desired result.

    Month1_CW =
    VAR SelectedFlag = SELECTEDVALUE(Flag_Table[Flag], FALSE)
    VAR CurrentDS = SELECTEDVALUE(VW_F_PIPELINE_HISTORY[DS])
    VAR CurrentMonth = SELECTEDVALUE(VW_F_PIPELINE_HISTORY[Month])
    VAR CurrentQuarter = SELECTEDVALUE(VW_F_PIPELINE_HISTORY[FISCAL_YEAR_QUARTER])
    VAR FiscalYear = SELECTEDVALUE(VW_F_PIPELINE_HISTORY[Fiscal Year])

    -- 1. Check if current DS is a quarter-end date
    VAR IsQuarterEndDate =
        CALCULATE(
            MAXX(
                FILTER(
                    ALL(VW_F_PIPELINE_HISTORY),
                    VW_F_PIPELINE_HISTORY[DS] = CurrentDS
                ),
                IF(VW_F_PIPELINE_HISTORY[is_last_day_of_fiscal_quarter], 1, 0)
            )
        ) = 1

    -- 2. Get last day of Month1 in that quarter
    VAR LastDayOfMonth1InQuarter =
        CALCULATE(
            MAX(VW_F_PIPELINE_HISTORY[DS]),
            FILTER(
                ALL(VW_F_PIPELINE_HISTORY),
                VW_F_PIPELINE_HISTORY[Month] = 1 &&
                VW_F_PIPELINE_HISTORY[FISCAL_YEAR_QUARTER] = CurrentQuarter &&
                VW_F_PIPELINE_HISTORY[Fiscal Year] = FiscalYear
            )
        )

    -- 3. Get CW_MTD value for that Month1 day, preserve all filters except DS
    VAR Month1Value =
        CALCULATE(
            SUM(VW_F_PIPELINE_HISTORY[CW_MTD]),
            FILTER(
                ALL(VW_F_PIPELINE_HISTORY[DS]),
                VW_F_PIPELINE_HISTORY[DS] = LastDayOfMonth1InQuarter
            )
        )

    -- 4. Show daily values if flag is false and it's Month1
    VAR ShowDaily =
        NOT SelectedFlag &&
        CurrentMonth = 1 &&
        CurrentDS <= TODAY()

    -- Final Output
    RETURN
    SWITCH(
        TRUE(),
        SelectedFlag && IsQuarterEndDate, Month1Value,
        ShowDaily, SUM(VW_F_PIPELINE_HISTORY[CW_MTD]),
        BLANK()
    )
v-csrikanth's avatar
v-csrikanth
Icon for Community Support rankCommunity Support
1 year ago

HI Anonymous 
Based on the attached pbix file please go throught and try the below measure that meets your rquirements.

The function ALL and ALL Selected, override the filters thats why the existing measure wouldnt work.

You can use a combination of Remove filters and Keep filters respecting existing filters (like SVP/RVP).

 

Try this measure:

 

Month1_CW_MTD_New =
VAR IsLastDayOfQuarter = SELECTEDVALUE(VW_D_DATE[IS_LAST_FISCAL_DAY_OF_QUARTER], FALSE)
VAR IsLastDayOfMonth   = SELECTEDVALUE(VW_D_DATE[IS_LAST_DAY_OF_MONTH], FALSE)
VAR CurrentMonth       = SELECTEDVALUE(VW_F_PIPELINE_HISTORY[Month])
VAR SelectedQuarter    = SELECTEDVALUE(VW_F_PIPELINE_HISTORY[FISCAL_YEAR_QUARTER])

 

VAR IsSpecialDay =
    IsLastDayOfQuarter ||
    (IsLastDayOfMonth && CurrentMonth = 1)

 

VAR Month1EndValue =
    CALCULATE(
        SUM(VW_F_PIPELINE_HISTORY[CW_MTD]),
        REMOVEFILTERS(VW_F_PIPELINE_HISTORY[is_last_day_of_fiscal_month]),
        FILTER(
            ALL(VW_D_DATE),
            VW_D_DATE[is_last_day_of_fiscal_month] = TRUE()
        ),
        KEEPFILTERS(
            VW_F_PIPELINE_HISTORY[Month] = 1 &&
            VW_F_PIPELINE_HISTORY[FISCAL_YEAR_QUARTER] = SelectedQuarter
        )
    )

 

VAR DailyValue =
    CALCULATE(
        SUM(VW_F_PIPELINE_HISTORY[CW_MTD]),
        KEEPFILTERS(
            VW_F_PIPELINE_HISTORY[Month] = 1
        )
    )

 

RETURN
IF(
    IsSpecialDay,
    Month1EndValue,
    DailyValue
)



If the issue has been resolved, can you mark the post as resolved. If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.