Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
itsmeanuj
Helper IV
Helper IV

Current Month, QTD, YTD Flag

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
ItemMonthValueCurrent Month FlagQTD FlagYTD Flag
ActualJan-2310011
ActualFeb-2312111
ActualMar-230000
ActualApr-230000
ActualMay-230000
ActualJun-230000
ActualJul-230000
ActualAug-230000
ActualSep-230000
ActualOct-230000
ActualNov-230000
ActualDec-230000
ForecastJan-2312000
ForecastFeb-2313000
ForecastMar-2315000
ForecastApr-2311000
ForecastMay-2310000
ForecastJun-2317000
ForecastJul-2321000
ForecastAug-2322000
ForecastSep-2314000
ForecastOct-2312000
ForecastNov-2310000
ForecastDec-2314000
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.