Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
i want to display sales amount for last 12 Completed month in Line Chart.
For Example:
If january 2022 once Came, that time line chart should show the data From Feb-2021 To Jan -2022. Only 12 MOnths
Please advice some measures for displaying completed 12 Months of data.
Solved! Go to Solution.
Hi @achanikya ,
If the last 12 months are always based on current date, you could simply use Relative Date filter as shown below:
2/11/2021-1/10/2022
Or if you want to dynamically change it based on date selection in slicer, please firstly create a date table for slicer, and then create a measure for filter:
ForSlicer = VALUES('Table'[Date])
Measure =
var _date= DATE(MAX('ForSlicer'[Date].[Year]), MAX('ForSlicer'[Date].[MonthNo]),1)
var _diff= DATEDIFF(MAX('Table'[Date]),_date,MONTH)
return IF(_diff>=0&& _diff<12,1,0)
2021/2/1-2022/1/10
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @achanikya ,
If the last 12 months are always based on current date, you could simply use Relative Date filter as shown below:
2/11/2021-1/10/2022
Or if you want to dynamically change it based on date selection in slicer, please firstly create a date table for slicer, and then create a measure for filter:
ForSlicer = VALUES('Table'[Date])
Measure =
var _date= DATE(MAX('ForSlicer'[Date].[Year]), MAX('ForSlicer'[Date].[MonthNo]),1)
var _diff= DATEDIFF(MAX('Table'[Date]),_date,MONTH)
return IF(_diff>=0&& _diff<12,1,0)
2021/2/1-2022/1/10
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@achanikya , if you have not selected a date then
//Last 12 based on today
new measure =
var _max = eomonth(today(),0)
var _min = eomonth(today(),-12)+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Assumes you are using joined date table.
But if you filtered Jan 21 and then want 12 months you need an independent date table
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |