Forum Discussion
Last 12 months data from current month
Hello,
1.Is there a way to display last 12 month from max date from sales tabel? So if its current month of Nov as for today, it'll show Dec 2021 - Nov 2022, when Dec become current month Jan-Dec? I have date table also. In date table max date will current date. But in sales table it will less dates.
2.Similary I need for last 4 Qtrs from current qtr
3.last 52 weeks from current week.
Need them in different measures.
Thanks !
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create measure with below dax formula
Combine = VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) VAR _value = YEAR ( cur_date ) * 100 + MONTH ( cur_date ) RETURN _valueRK = RANKX(ALL('Table'),[Combine],,ASC,Dense)Measure = VAR last_date = MAXX ( ALL ( 'Table' ), [Date] ) VAR rk = CALCULATE ( [RK], FILTER ( ALL ( 'Table' ), [Date] = last_date ) ) VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [RK] > rk - 12 ) ) VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) RETURN IF ( cur_date IN tmp, 1, 0 )Year and Qtr = VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) VAR cur_qtr = [Qtr] VAR _value = YEAR ( cur_date ) * 100 + cur_qtr RETURN _valueRK For Qtr = RANKX(ALL('Table'),[Year and Qtr],,ASC,Dense)Condition for Qtr = VAR last_date = MAXX ( ALL ( 'Table' ), [Date] ) VAR rk = CALCULATE ( [RK For Qtr], FILTER ( ALL ( 'Table' ), [Date] = last_date ) ) VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [RK For Qtr] > rk - 4 ) ) VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) RETURN IF ( cur_date IN tmp, 1, 0 )Year and Week Num = VAR cur_data = SELECTEDVALUE ( 'Table'[Date] ) VAR wn = [Week Num] RETURN YEAR ( cur_data ) * 100 + wnRK for Week Num = RANKX(ALL('Table'),[Year and Week Num],,ASC,Dense)Condition for Week Num = VAR last_date = MAXX ( ALL ( 'Table' ), [Date] ) VAR rk = CALCULATE ( [RK for Week Num], FILTER ( ALL ( 'Table' ), [Date] = last_date ) ) VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [RK for Week Num] > rk - 52 ) ) VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) RETURN IF ( cur_date IN tmp, 1, 0 )3. add a table visual with fields, add measure to it filter pane
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quick
7 Replies
- djurecic
Super User
Anonymous , instead of months, please try using calendar months in the filter. This should give you the result you are looking for.
Please accept as solution if this has answered your question.
Calendar month will give values for current year. This is not the solution I am looking
Thanks !
This was very helpful thanks.
- djurecic
Hi Anonymous ,
Are you looking for the values for the past 12 months, or a list of the past 12 months?
Please use a date table if you do not already have one: https://www.sqlbi.com/tools/dax-date-template/
Seems like you might be able to use a relative date slicer where you can specify past 12 months and use that as a filter on your other visuals.
https://learn.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
Please accept as solution if this has answered your question.
Hi djurecic ,
This will not work.
Example I want last 12 months total from maxdate of my sales table.
I have sales till 31st of Oct. Idealy 12 month from Oct 2022 from Nov 21- Oct 22.
IF I use relative dates it will give 12 months from today as shown in below image
Thanks !
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create measure with below dax formula
Combine = VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) VAR _value = YEAR ( cur_date ) * 100 + MONTH ( cur_date ) RETURN _valueRK = RANKX(ALL('Table'),[Combine],,ASC,Dense)Measure = VAR last_date = MAXX ( ALL ( 'Table' ), [Date] ) VAR rk = CALCULATE ( [RK], FILTER ( ALL ( 'Table' ), [Date] = last_date ) ) VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [RK] > rk - 12 ) ) VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) RETURN IF ( cur_date IN tmp, 1, 0 )Year and Qtr = VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) VAR cur_qtr = [Qtr] VAR _value = YEAR ( cur_date ) * 100 + cur_qtr RETURN _valueRK For Qtr = RANKX(ALL('Table'),[Year and Qtr],,ASC,Dense)Condition for Qtr = VAR last_date = MAXX ( ALL ( 'Table' ), [Date] ) VAR rk = CALCULATE ( [RK For Qtr], FILTER ( ALL ( 'Table' ), [Date] = last_date ) ) VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [RK For Qtr] > rk - 4 ) ) VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) RETURN IF ( cur_date IN tmp, 1, 0 )Year and Week Num = VAR cur_data = SELECTEDVALUE ( 'Table'[Date] ) VAR wn = [Week Num] RETURN YEAR ( cur_data ) * 100 + wnRK for Week Num = RANKX(ALL('Table'),[Year and Week Num],,ASC,Dense)Condition for Week Num = VAR last_date = MAXX ( ALL ( 'Table' ), [Date] ) VAR rk = CALCULATE ( [RK for Week Num], FILTER ( ALL ( 'Table' ), [Date] = last_date ) ) VAR tmp = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), [RK for Week Num] > rk - 52 ) ) VAR cur_date = SELECTEDVALUE ( 'Table'[Date] ) RETURN IF ( cur_date IN tmp, 1, 0 )3. add a table visual with fields, add measure to it filter pane
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickHi,
I am in the same situation and was trying to apply this solution but this does not work when applied to matrix visual as visual level filter.
Appreciate if you have any suggestion.
Regards,
Sagar
