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

 

 

 

  • TravisGlanzer's avatar
    TravisGlanzer
    Frequent Visitor
    5 years ago

    Doesn't look like my company will let me give access to anyone with a link:

    This site doesn't seem to allow me to just post the pbix?

    • 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.