none
DAX - Max Measure RRS feed

  • Question

  • Hi, am developing a first Power BI report and one of the requirement is to default power BI report to most recent Quarter data available in the snapshot table.

    Snapshot is frozen data taken after end of each Quarter. so for now I have 10 Quarters of Snapshots.

    Field names snapshot Period: Reporting_Year (2017,2018,2019 so far), Reporting_Quarter (only 1,2,3,4).

    I have two slicers 1. Reporting Year 2. Reporting Quarter. so, if someone opens report now it must display 2019.Q2 data by default. For that 

    I derived MaxYearMeasure and its working correctly.

    CALCULATE(MAXA(FilingSnapshot[Snapshot_Year]),ALL(FilingSnapshot[Snapshot_Year]))

    Now for this given MaxYearMeasure, I want Max Quarter and having difficult to derive it. Max Quarter must be based on MaxYear.

    Thanks in advance,

    Kumar

    Monday, August 5, 2019 9:23 PM

Answers

  • There would be a way of doing this across 2 columns, but date dimensions are so small that typically I would just create a combined year and quarter attribute and filter on the max of that.

    You could simply create a calculated column with [Snapshot_Year] * 10 + [Snapshot_Quarter] which would give you a value like 20192 for 2019 Q2 

    In fact you could probably extend this further to calculate an IsCurrentQuarter calc column. Then in your report you just filter for IsCurrentQuarter = 1 and this will update dynamically when ever you add a new quarter.

    IsCurrentQuarter = IF( MAXX( FilingSnapshot,  [Snapshot_Year] * 10 + [Snapshot_Quarter]) =  [Snapshot_Year] * 10 + [Snapshot_Quarter], 1, 0)


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Kumar5555 Wednesday, August 7, 2019 2:02 PM
    Tuesday, August 6, 2019 11:36 PM
    Moderator

All replies

  • There would be a way of doing this across 2 columns, but date dimensions are so small that typically I would just create a combined year and quarter attribute and filter on the max of that.

    You could simply create a calculated column with [Snapshot_Year] * 10 + [Snapshot_Quarter] which would give you a value like 20192 for 2019 Q2 

    In fact you could probably extend this further to calculate an IsCurrentQuarter calc column. Then in your report you just filter for IsCurrentQuarter = 1 and this will update dynamically when ever you add a new quarter.

    IsCurrentQuarter = IF( MAXX( FilingSnapshot,  [Snapshot_Year] * 10 + [Snapshot_Quarter]) =  [Snapshot_Year] * 10 + [Snapshot_Quarter], 1, 0)


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Kumar5555 Wednesday, August 7, 2019 2:02 PM
    Tuesday, August 6, 2019 11:36 PM
    Moderator
  • Thank you, Darren. I have added a new column to indicate Latest Quarter yes/No and then I followed Patrick to default the report.

    https://www.youtube.com/watch?time_continue=224&v=lkHFpmA4SJ4

    Thank you,

    Kumar.

    Wednesday, August 7, 2019 3:46 PM
  • Alternate method:

    Step 1: Add a new column

                DateKey = DATE(Snapshot_Year,Snapshot_Quarter*3,1)

    Step 2: Add a new column

               MaxQuarter = IF(CaseFilingSnapshot[DateKey]=MAX(CaseFilingSnapshot[DateKey]),1,0)

    By creating a date column, and creating/adding a Date/Calendar table, will give us lot of flexibility to use built-in Calendar functions.

    Friday, August 16, 2019 4:05 AM