cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 !

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

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
_value
``````

``RK = 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
_value
``````
``RK 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 + wn
``````
``RK 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 7
Anonymous
Not applicable

Hi @Anonymous ,

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
_value
``````

``RK = 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
_value
``````
``RK 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 + wn
``````
``RK 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

Frequent Visitor

Hi,

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

Super User

@Anonymous , instead of months, please try using calendar months in the filter. This should give you the result you are looking for.

Frequent Visitor

Anonymous
Not applicable

Calendar month will give values for current year. This is not the solution I am looking

Thanks !

Super User

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

Anonymous
Not applicable

Hi @djurecicK2 ,

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 !

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.