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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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