Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I couldn't find anything in the forum or other website that related to what I'm trying to accomplished.
Dec-2020 | Jan-2021 | Feb-2021 | Mar-2021 | Apr-2021 | May-2021 | Jun-2021 | Jul-2021 | Aug-2021 | Sep-2021 |
Oct-2021 | Nov-2021 | Dec-2021 |
What I'm trying achieve is I want a rolling month including month of Dec-2020. Basically, if month of Jan-2021 has ended I want it dynamically to exclude month of Dec-2020. If today month is still Jan-2021 then include the previous month of Dec-2020.
I have created a formula but I don't if it will work and continue the trending, can some help me review my DAX?
RollingMonth = IF(Table[Date] <= TODAY(),
IF(DATEDIFF(Table[Date], TODAY(),MONTH)+10 <= 11,
TRUE(),FALSE()),FALSE())
Thank you for your help.
HI @Anonymous,
Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@Anonymous , Not very clear with date table
example //last 12
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1) ,13,MONTH)) //+13 from last to next 12
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
There @amitchandak
Thank you for your response, upon reviewing your formula. I don't want to sum anything or count I want a dynamic field as filter/slicer where I can select true or false.
@Anonymous , A new column like
RollingMonth = IF(Table[Date] > eomonth(TODAY(),-2) && Table[Date] <= eomonth(TODAY(),12) ,
TRUE(),FALSE())
or
RollingMonth = IF(Table[Date] > eomonth(TODAY(),-2) && Table[Date] <= eomonth(TODAY(),11) ,
TRUE(),FALSE())
I tried the last formula. It seems to be working but I don't want Nov-2020.
RollingMonth = IF(Table[Date] > EOMONTH(TODAY(),-2) && Table[Date] <= EOMONTH(TODAY(),11) ,TRUE(),FALSE())
HI @Anonymous,
You can try to use the following formula, I added conditions to compare with the end date of the current month and use this in EOMONTH function:
RollingMonth =
VAR cDate =
TODAY ()
RETURN
IF (
Table[Date]
> EOMONTH ( cDate, IF ( DAY ( cDate ) = DAY ( EOMONTH ( cDate, 0 ) ), -2, -1 ) )
&& Table[Date] <= EOMONTH ( cDate, 11 ),
TRUE (),
FALSE ()
)
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
It doesn't work, is only showing Jan-2021. Basically I want to include the previous Month of Dec-2020 if month of Jan-2021 hasn't pass yet. Let say If today is Feb-2021 then Dec-2020 should not be included in the rolling month.
I hope that make sense. Thank you
Hi @Anonymous,
So you mean the conversion only exists when the current date is in the ranges of the first two months?
If this is the case, you can try to use this formula, it contains the condition to check if the current date is February to ignore December data:
RollingMonth =
VAR cDate =
TODAY ()
VAR prevMonth =
EOMONTH ( cDate, IF ( MONTH ( cDate ) = 2, -3, -1 ) )
RETURN
IF (
Table[Date] > EOMONTH ( prevMonth, -11 )
&& Table[Date] <= prevMonth,
TRUE (),
FALSE ()
)
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |