Forum Discussion

TravisGlanzer's avatar
TravisGlanzer
Frequent Visitor
5 years ago
Solved

Max month in table with data

I am trying to create a DAX that gives me the last (Max) month's result in each quarter.  But if the last month is mid quarter then I would like the max month where the table actually has data with a...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, TravisGlanzer 

    Try  measure as below:

     

    Qtr_monthno = 
    RANKX (
        FILTER ( ALL ( 'BdgDate' ), BdgDate[Quarter] = MAX ( BdgDate[Quarter] ) ),
        CALCULATE ( MAX ( BdgDate[Date] ) ),
        ,
        ASC
    )
//Rank of month in each quarter
    Max_Qtr.No = 
MAXX (
    FILTER (
        BdgDate,
        (
            BdgDate[Quarter] = MAX ( BdgDate[Quarter] )
                && [ReportBuilderEntCurrentDonorBase] <> BLANK ()
        )
    ),
    [Qtr_monthno]
)
//max month where data exists for actuals
    Result = 
CALCULATE (
    MAX ( 'VW_AGGR_DONOR_BASE'[Donor Base] ),
    FILTER (
        ALL ( BdgDate ),
        ( BdgDate[Quarter] = MAX ( BdgDate[Quarter] ) )
            && [Max_Qtr.No] = MAXX ( BdgDate, [Max_Qtr.No] )
    )
)

     

     

     

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi,  TravisGlanzer 

Uh,.... I can access it, but it seems that you uploaded a pbix file that is not related to the problem. I only saw a clustered column chart in the report.

 

Best Regards,
Community Support Team _ Eason

 

  • TravisGlanzer's avatar
    TravisGlanzer
    Frequent Visitor
    5 years ago

    My appologies.  It is the same DAX but a different graph.  If you drill down to month you will see that the actual has results Jan through May but in the quarter view, i do not show any Q2 result.  My hope was to show the max month (i.e. May for this month) in the quarter level rather than blank which is June.

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi,  TravisGlanzer 

      For you first question ,you may need to  replace "SUM" with "MAX".
      Try to change your measure "ReportBuilderEntCurrentDonorBaser" as follows:

       

      Measure1 = MAX('VW_AGGR_DONOR_BASE'[Donor Base])

       

      For you second question, I did not find it in the demo you provided. The visual filter measure “Check column” seems to filter the data well.

      Best Regards,
      Community Support Team _ Eason

      • TravisGlanzer's avatar
        TravisGlanzer
        Frequent Visitor
        5 years ago

        Appreciate your attention to this qusetion.  I am afraid that the max dax will give me the max result which works this month but what if the donor base decreased? I am looking for the data from the max month where data exists for actuals. 

         

        I can not use the check digit in this situation as when i do, I then do not see the budget results for the remaining year.  I would love to apply just hte check digit to actuals only but no point when it already does it by having no data.  I attempted to put check digit filter in my max date dax for current donor base but it didn't help to eliminate June so we only show May's result in the 2021 Q2.