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 actual results.  

 

for the current data, I do not have June data yet so I want to report May's data for Actuals.  

 

When i remove FiscalDateField to get the max month's result per quarter, I get nothing for 2021 Qtr 2 because Jun is blank.  

I have a checkcolumn that I can select and then I get the correct result but then I loose the future budget values in the last column.  

How can I express in each quarter the actual result from the max month where I actually have results for Actual but still present all year's max quarterly budget numbers?

 

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.

     

     

11 Replies