Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to show the last 6 months and next 6 months data in charts.
In my table i have the dates as below.
Now in my chart i would like to show data of current month to last 6 months and next 6 months.
I have written below calculated column and it is calculating the last 6 months and next 6 months regardless of the year.
Last&next6months = IF(AND(MONTH('Month Year'[Date])>=MONTH(TODAY())-6,MONTH('Month Year'[Date])<=MONTH(TODAY())),"Last 6 months", IF(AND(MONTH('Month Year'[Date])>=MONTH(TODAY()),MONTH('Month Year'[Date])<=MONTH(TODAY())-12),"","Next 6 months"))
Output:-
Any better solution or any suggestions for this.
Thanks,
Mohan V
Solved! Go to Solution.
@Anonymous,
You may add a calculated column as shown below.
Column = VAR MonthDiff = IF ( 'Month Year'[Date] <= TODAY (), - DATEDIFF ( 'Month Year'[Date], TODAY (), MONTH ), DATEDIFF ( TODAY (), 'Month Year'[Date], MONTH ) ) RETURN SWITCH ( TRUE (), MonthDiff >= -5 && MonthDiff <= 0, "Last 6 months", MonthDiff >= 1 && MonthDiff <= 6, "Next 6 months", "other" )
hi
I used same DAX for next six months display. it didnt work.
@Anonymous,
You may add a calculated column as shown below.
Column = VAR MonthDiff = IF ( 'Month Year'[Date] <= TODAY (), - DATEDIFF ( 'Month Year'[Date], TODAY (), MONTH ), DATEDIFF ( TODAY (), 'Month Year'[Date], MONTH ) ) RETURN SWITCH ( TRUE (), MonthDiff >= -5 && MonthDiff <= 0, "Last 6 months", MonthDiff >= 1 && MonthDiff <= 6, "Next 6 months", "other" )