Max month in table with data

[TravisGlanzer]

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?

[v-easonf-msft]

Hi, @TravisGlanzer 

For the missing value of future budget in picture, you can take a try to add  "+0" in the end of  your measure "Report Builder Actual"  and "Reprot Build Budget".

If it doesn't work, please share the sample pbix file for further research.

Best Regards,
Community Support Team _ Eason

[TravisGlanzer]

Thanks for the response.  Didn't get the +0 to work.  here is the sample file.  You can see I do not have a 2Q result for Current because the last result was in May which is the nmber I would like to represent for the quarter.  https://vitalant-my.sharepoint.com/:u:/g/personal/009247_bloodsystems_org/EVfjm7Rl8nBLnCBGJkCZRdwBYN... 

Hope this works.

[v-easonf-msft]

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]

Try this one please:

https://1drv.ms/u/s!AgEi0DVyal4TghE3TuwvRUu8QAb1?e=vB2bg8

[v-easonf-msft]

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]

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]

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]

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 · Accepted solution]

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.

[TravisGlanzer]

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?

[TravisGlanzer]

Try this one please: 

https://1drv.ms/u/s!AgEi0DVyal4TghE3TuwvRUu8QAb1?e=vB2bg8