Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 |
Solved! Go to 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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
74 | |
63 | |
51 | |
47 |
User | Count |
---|---|
218 | |
86 | |
64 | |
63 | |
60 |