Current Month, QTD, YTD Flag

[itsmeanuj]

Hi Guys,

I want to create a dynamic flag (Current month, QTD, YTD) on the basis of months in my data (please see the sample below). The flag should be based on the Item value "Actuals". If i is >0 then we should consider that month as the latest one

			Expected Output
Item	Month	Value	Current Month Flag	QTD Flag	YTD Flag
Actual	Jan-23	10	0	1	1
Actual	Feb-23	12	1	1	1
Actual	Mar-23	0	0	0	0
Actual	Apr-23	0	0	0	0
Actual	May-23	0	0	0	0
Actual	Jun-23	0	0	0	0
Actual	Jul-23	0	0	0	0
Actual	Aug-23	0	0	0	0
Actual	Sep-23	0	0	0	0
Actual	Oct-23	0	0	0	0
Actual	Nov-23	0	0	0	0
Actual	Dec-23	0	0	0	0
Forecast	Jan-23	12	0	0	0
Forecast	Feb-23	13	0	0	0
Forecast	Mar-23	15	0	0	0
Forecast	Apr-23	11	0	0	0
Forecast	May-23	10	0	0	0
Forecast	Jun-23	17	0	0	0
Forecast	Jul-23	21	0	0	0
Forecast	Aug-23	22	0	0	0
Forecast	Sep-23	14	0	0	0
Forecast	Oct-23	12	0	0	0
Forecast	Nov-23	10	0	0	0
Forecast	Dec-23	14	0	0	0

[v-shex-msft · Accepted solution]

HI @itsmeanuj,

In fact, you do not need to create multiple columns for these flags. You could add a calculate column to use table date compare with current system date if it included in current month, quarter, year return 1 and aggregate these conditions results.(the bigger number means it matched more conditions and you can simply add more detail level flags and they not affect the global filter usages)

Flag =
VAR yearFlag =
    IF ( YEAR ( TODAY () ) = YEAR ( [Date] ), 1, 0 )
VAR quarterFlag =
    IF ( yearFlag = 1 && QUARTER ( TODAY () ) = QUARTER ( [Date] ), 1, 0 )
VAR monthFlag =
    IF ( yearFlag = 1 && MONTH ( TODAY () ) = MONTH ( [Date] ), 1, 0 )
RETURN
    yearFlag + quarterFlag + monthFlag

For the filter operation, you only need to check the flag number equal or larger than current level.(e.g. year level >=1; quarter level >=2; month level >=3) 

Regards,

Xiaoxin Sheng