Dynamically get top 12 time periods when switching between date field parameters

Question

All -&nbsp;&nbsp;I am trying to show the most recent twelve date results for each selection "Year", "Quarter and Year", and "Month and Year" which are field parameters from my date table 'date'.&nbsp;Date Table is the following:Date = VAR Days = CALENDAR(min(test[PIF Date]),max(test[PIF Date]))RETURN ADDCOLUMNS (&nbsp; &nbsp; Days,&nbsp; &nbsp; "Year", YEAR ( [Date] ),&nbsp; &nbsp; "Month", FORMAT ( [Date], "mmmm" ),&nbsp; &nbsp; "Month Number", MONTH([Date]),&nbsp; &nbsp; "Start of the Month", DATE( YEAR([Date]), MONTH([Date]),1),&nbsp; &nbsp; "End of the Month", EOMONTH([Date],0),&nbsp; &nbsp; "Quarter and Year", "Q"&amp;FORMAT([Date],"Q YYYY"),&nbsp; &nbsp; "Month and Year", FORMAT([Date],"MMM YYYY"))&nbsp;Graphs show volume by field parameter dates.&nbsp;Volume calculation =&nbsp;Volume = CALCULATE(SUM(test[Volume]), USERELATIONSHIP(test[Date],'Date'[Date]))&nbsp;&nbsp;Do I need to write a new measure for Volume that only pulls top 12 most recent date periods?&nbsp; In the Quarter and Year instance I only want to show Q1-Q4 for 2020 - 2022 volume. How do I write this?&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Yes, you can create a measure that checks which data format is in scope and then filter the 12 most recent dates.Something like this:&nbsp;Volume Last 12 Periods =

RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Date[Year] ), 
            CALCULATE(
                [Volume],
				DATESINPERIOD(
					'Date'[Date],
					MAX('Date'[Date]),
					-12,
					YEAR
				)
    )

),
        ISINSCOPE ( Date[Quarter] ) &amp;&amp; ISINSCOPE ( Date[Year] ), 
            CALCULATE(
                [Volume],
				DATESINPERIOD(
					'Date'[Date],
					MAX('Date'[Date]),
					-12,
					QUARTER
				)
    ),
    ISINSCOPE ( Date[Month] ) &amp;&amp; ISINSCOPE ( Date[Year] ), 
            CALCULATE(
                [Volume],
				DATESINPERIOD(
					'Date'[Date],
					MAX('Date'[Date]),
					-12,
					MONTH
				)
    )
    )

&nbsp;Kind regards,JoséPlease mark this answer as the solution if it resolves your issue.Appreciate your kudos! 🙂&nbsp;

rcorn · Answer

This did not work, it did not get rid of dates prior to the most recent 12 time periods and sum of the volume was inaccurate.

anonymous · Answer

Would you mind sharing a pbix file with some sample data?

rcorn · Answer

I am not certain about how to share, there is no choose file option on the reply.

rcorn · Answer

I want bottom right graph to look like other three when you switch between dates. (top 12 for each)&nbsp;&nbsp;All I have for my fact table columns is uniuqe ID, volume amount, volume date.&nbsp;I show DAX for data table, volume, and field parameters below.&nbsp;My date table :Date Table is the following:Date =VAR&nbsp;Days =&nbsp;CALENDAR(min(test[PIF Date]),max(test[PIF Date]))RETURN&nbsp;ADDCOLUMNS&nbsp;(&nbsp; &nbsp; Days,&nbsp; &nbsp;&nbsp;"Year",&nbsp;YEAR&nbsp;( [Date] ),&nbsp; &nbsp;&nbsp;"Month",&nbsp;FORMAT&nbsp;( [Date],&nbsp;"mmmm"&nbsp;),&nbsp; &nbsp;&nbsp;"Month Number",&nbsp;MONTH([Date]),&nbsp; &nbsp;&nbsp;"Start of the Month",&nbsp;DATE(&nbsp;YEAR([Date]),&nbsp;MONTH([Date]),1),&nbsp; &nbsp;&nbsp;"End of the Month",&nbsp;EOMONTH([Date],0),&nbsp; &nbsp;&nbsp;"Quarter and Year",&nbsp;"Q"&amp;FORMAT([Date],"Q YYYY"),&nbsp; &nbsp;&nbsp;"Month and Year",&nbsp;FORMAT([Date],"MMM YYYY"))&nbsp;My volume calculation is :&nbsp;Volume =&nbsp;CALCULATE(SUM(test[Volume]),&nbsp;USERELATIONSHIP(test[Date],'Date'[Date]))&nbsp;Field parameter:&nbsp;Parameter = {&nbsp; &nbsp; ("Year", NAMEOF('Date'[Year]), 0),&nbsp; &nbsp; ("Quarter and Year", NAMEOF('Date'[Quarter and Year]), 1),&nbsp; &nbsp; ("Month and Year", NAMEOF('Date'[Month and Year]), 2)}

Forum Discussion

Dynamically get top 12 time periods when switching between date field parameters

5 Replies

Related content

Dynamic RLS + Slicer Role Switching Issue

Field parameter with switch

Dynamic subcription parameter not saving

Sum dynamic parameter fields multiple selected parameter

Switch Parameters