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 1 and Month 2 values are visible along with Month 3(which lies in the last month of quarter) values. If I dont select any flag for quarter/month last day then the measure is working fine.

I have created a measure below and it is working absolutely fine and giving me the correct value when I'm using either of my flags in the data ( is_last_fiscal_day_of_quarter or is_last_day_of_month).

But the issue is that the data is not getting filtered when I'm using any other cuts from my table like if I'm trying to filter on a SVP/RVP etc the measure value is not filtering if I've applied either of the flags. Instead of ALL function I've tried using ALLSELECTED as well but its also not working.

Expected Result: I want the measure to work with both the flags and without them also and data to get filtered by any cut in the dataset.

I'm attaching a sample PBIX for reference. Quick Help would be highly appreciated.

PBIX LINK : https://drive.google.com/file/d/1BpMg28UgVwFRnGt6QEh0OqTwWOzXXkkW/view?usp=sharing

Measure Created:

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]),

FILTER(

ALL(VW_F_PIPELINE_HISTORY),

VW_F_PIPELINE_HISTORY[Month] = 1 &&

VW_F_PIPELINE_HISTORY[FISCAL_YEAR_QUARTER] = SelectedQuarter &&

VW_F_PIPELINE_HISTORY[is_last_day_of_fiscal_month] = TRUE()

)

VAR DailyValue =

CALCULATE(

SUM(VW_F_PIPELINE_HISTORY[CW_MTD]),

FILTER(

VW_F_PIPELINE_HISTORY,

VW_F_PIPELINE_HISTORY[Month] = 1

)

RETURN

IF(

IsSpecialDay,

Month1EndValue,

DailyValue

)

Need Help

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

8 Replies

Anonymous
Not applicable
1 year ago
Objective: Dynamic Monthly CW_MTD Values by Flags
Need to create dynamic logic for each fiscal month (Month 1 and 2) that:
On Daily View (No Flags):
Show the daily CW_MTD value for each date within the respective fiscal month.
When a Flag is Used (Month-End or Quarter-End):
Show the final CW_MTD value of that fiscal month, only if:
The row belongs to that month (Month 1 or 2), and
The flag (IS_LAST_DAY_OF_MONTH or IS_LAST_FISCAL_DAY_OF_QUARTER) is set to TRUE.
Able to filter data on any cuts in the dataset.

Note: Month 3 uses a simple calculation and is giving correct value and filtering is also working fine. Need to create seperate measures for Month 1 and Month 2
techies
Super User
1 year ago
Hi Anonymous please try this

Month1_CW_MTD_Newf =
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]),
KEEPFILTERS(VW_F_PIPELINE_HISTORY),
FILTER(
VW_F_PIPELINE_HISTORY,
VW_F_PIPELINE_HISTORY[Month] = CurrentMonth &&
VW_F_PIPELINE_HISTORY[FISCAL_YEAR_QUARTER] = SelectedQuarter &&
VW_F_PIPELINE_HISTORY[is_last_day_of_fiscal_month] = TRUE()
)
)
VAR DailyValue =
CALCULATE(
SUM(VW_F_PIPELINE_HISTORY[CW_MTD]),
FILTER(
VW_F_PIPELINE_HISTORY,
VW_F_PIPELINE_HISTORY[Month] = 1
)
)
RETURN
IF(
IsSpecialDay,
Month1EndValue,
DailyValue
)
- Anonymous
  Not applicable
  1 year ago
  I've tried using KEEPFILTERS as a function. If I use the above measure I'm getting numbers for last of the month 1and month 2 when I use is_last_day_fiscal_quarter = TRUE but the numbers I'm getting is wrong. Ideally I should get $3.10M in Month 1 and $8.07M in Month 2 as values when I select is_last_day_fiscal_quarter = True.
  
  Using Fiscal_Quarter Flag ( Wrong Numbers):
  
  Correct Numbers:
  - techies
    Super User
    1 year ago
    ok
v-csrikanth
Community 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.
v-csrikanth
Community Support
1 year ago
Hi Anonymous

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
- Anonymous
  Not applicable
  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()
  )