Forum Discussion

rcorn's avatar
rcorn
Frequent Visitor
4 years ago

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

All -    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'.   Date T...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi

 

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:

 

Volume Last 12 Periods = 

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

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

 

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

rcorn's avatar
rcorn
Frequent Visitor
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • rcorn's avatar
      rcorn
      Frequent Visitor
      4 years ago

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

    • rcorn's avatar
      rcorn
      Frequent Visitor
      4 years ago

      I want bottom right graph to look like other three when you switch between dates. (top 12 for each)

       

       

      All I have for my fact table columns is uniuqe ID, volume amount, volume date.

       

      I show DAX for data table, volume, and field parameters below.

       

      My date table :

      Date Table is the following:

      Date =
      VAR Days = CALENDAR(min(test[PIF Date]),max(test[PIF Date]))
      RETURN ADDCOLUMNS (
          Days,
          "Year", YEAR ( [Date] ),
          "Month", FORMAT ( [Date], "mmmm" ),
          "Month Number", MONTH([Date]),
          "Start of the Month", DATE( YEAR([Date]), MONTH([Date]),1),
          "End of the Month", EOMONTH([Date],0),
          "Quarter and Year", "Q"&FORMAT([Date],"Q YYYY"),
          "Month and Year", FORMAT([Date],"MMM YYYY")
      )
       
      My volume calculation is : 
      Volume = CALCULATE(SUM(test[Volume]), USERELATIONSHIP(test[Date],'Date'[Date]))
       
      Field parameter: 
      Parameter = {
          ("Year", NAMEOF('Date'[Year]), 0),
          ("Quarter and Year", NAMEOF('Date'[Quarter and Year]), 1),
          ("Month and Year", NAMEOF('Date'[Month and Year]), 2)
      }