Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

MAX Date filter

Good Day,

I have an issue where a DAX formula is not ouputting correctly. Essentially I wrote formulas to calculate the value difference of items from a previous month, which ouputs correctly. Now I want to filter on the MAX selected date (wrote this in DAX) for those values but it does not work.

 

1. The matrix output on the far left is correct
2. The table output in the middle via a DAX filter does not work ([Latest Month], [Date Checker]) Code can be found in PowerBI file
3. The table output on the right (manual filter) works perfectly.

I'm not sure where the formula is breaking.I believe it is somewhere along the lines because of the relationship between the two tables in PowerBI.

 

Let me know if I need to furnish more details.Please find the links to the excel base data and Power BI File that may assist.

 

Excel - https://www.dropbox.com/s/ifqq493hvw781ub/Test.pbix?dl=0

PowerBI - https://www.dropbox.com/s/8cmm6vpbpxwh0bo/Test.xlsx?dl=0



4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Not checked your file. But you can have measure like ?

     

    measure =
    var _max = maxx(allselected(Date), Date[Date])
    return
    calculate([Value measure], filter(Date,Date[Date]= _max))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak,

       

      Slightly confused on the [Value measure] syntax - is that self-referencing ?

       

      I tried to do it this way to get my latest month to run a filter but it does not work. 

       

      Latest Month =


      VAR CurrentYearMonth =
       
      MAXX(ALLSELECTED('tertiary vw_insights_ageing_profile'),'tertiary vw_insights_ageing_profile'[Date Link])
       
      Return IF(max('tertiary vw_insights_ageing_profile'[Date Link]) = CurrentYearMonth,1,0)

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Anonymous , Try like

        measure =

        VAR CurrentYearMonth = MAXX(ALLSELECTED('tertiary vw_insights_ageing_profile'),'tertiary vw_insights_ageing_profile'[Date Link])
        Return

           IF(format(max('tertiary vw_insights_ageing_profile'[Date Link]),"YYYYMM") = format(CurrentYearMonth,"YYYYMM"),1,0)

         

        You can use today() in place of MAXX(ALLSELECTED('tertiary vw_insights_ageing_profile'),'tertiary vw_insights_ageing_profile'[Date Link]) , if needed