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 

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.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    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.