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 

It seems that I don’t have permission to access it.

Can you check the permission  and share the link again?

Best Regards,
Community Support Team _ Eason

 

 

 

  • 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