using dax formula to calculate the previous month total

[Anonymous]

My question sounds simple, but i have not been able to calculate the prior/previous month total using a dax formula. I have a formula that calulates the current/latest month total and here is the formula for the latest month: 

ar_0_30 = CALCULATE(sum(AR_Data[M_BALN_POST]),filter( AR_Data,AR_Data[Report Date] = MAX(AR_Data[Report Date])),FILter(AR_Data,(AR_Data[Age Group - Discharge] = " 0-30")))

When i try and add previous or '-1' in the report date filter, it would fail. The format of the AR_Data[Report Date] is mm/dd/yyyy

Help will be greatly appriciated.

Thank you in advance

[TheoC]

Hi @Anonymous 

There looks to be a lot of comments and potential solutions already provided, so my apologies if I am just adding to the confusion, however, can you try the below:

PriorMonthToDate =

VAR _CurMth = MONTH ( TODAY() )
VAR _CurYr = YEAR ( TODAY() )
VAR _CurDay = DAY ( MAX ( AR_Data[Report Date] ) )
VAR _LastDay = DATE ( _CurYr , _CurMth -1, _CurDay )
VAR _FirstDay = DATE ( _CurYr, _CurMth -1, 1 )

RETURN

CALCULATE ( SUM ( AR_Data[M_BALN_POST] ) , FILTER ( 'AR_Data' , AR_Data[Report Date] >= _FirstDay && AR_Data[Report Date] <= _LastDay ) , FILTER ( 'AR_Data' , AR_Data[Age Group - Discharge] = " 0-30" ) )

All the best! 

Theo 🙂

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

ar_0_30 =
CALCULATE(SUM('Table'[M_BALN_POST]),FILTER(ALLSELECTED('Table'),
MAX('Table'[Report Date])>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))&&
MAX('Table'[Report Date])<=TODAY()
&&'Table'[Age Group - Discharge]="0-30"))

2. Click on the blank space of the page and place [Report Date] in the Filter to filter the entire page.

3. Result:

Total in the most recent month:

Filter type – Relative Date, Is in the last , 1, Month。

The results are displayed for the most recent month:

Total last month:

Filter type – Relative Date, Is in the last , 1, calendar months.

The results are shown as last month:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

form at of the report date. I dont think i can share the pbxi file.

[Samarth_18]

Hi @Anonymous 

You can try below code:-

PreviousMonth =
CALCULATE (
    SUM ( AR_Data[M_BALN_POST] ),
    FILTER (
        AR_Data,
        AR_Data[Report Date]
            = MAX (
                DATE ( YEAR ( AR_Data[Report Date] ), MONTH ( AR_Data[Report Date] ) - 1, DAY ( AR_Data[Report Date] ) )
            )
    ),
    FILTER ( AR_Data, ( AR_Data[Age Group - Discharge] = " 0-30" ) ) //change this filter condition according to your requirement
)

Thanks,

Samarth

[Anonymous]

thank you for that reply, it did not seem to work.

this is the error i am getting now

The MAX function only accepts a column reference as an argument.

[Samarth_18]

Can you try below code:-

PreviousMonth =
CALCULATE (
    SUM ( AR_Data[M_BALN_POST] ),
    FILTER (
        AR_Data,
        AR_Data[Report Date]
            = DATE ( MAX ( YEAR ( AR_Data[Report Date] ) ), MONTH ( MAX ( AR_Data[Report Date] ) ) - 1, DAY ( MAX ( AR_Data[Report Date] ) ) )
    ),
    FILTER ( AR_Data, ( AR_Data[Age Group - Discharge] = " 0-30" ) )
)

[Anonymous]

same error

The MAX function only accepts a column reference as an argument.

[Samarth_18]

Sorry my bad. This would work for sure 🙂

PreviousMonth =
CALCULATE (
    SUM ( AR_Data[M_BALN_POST] ),
    FILTER (
        AR_Data,
        AR_Data[Report Date]
            = DATE ( YEAR ( MAX ( AR_Data[Report Date] ) ), MONTH ( MAX ( AR_Data[Report Date] ) ) - 1, DAY ( MAX ( AR_Data[Report Date] ) ) )
    ),
    FILTER ( AR_Data, ( AR_Data[Age Group - Discharge] = " 0-30" ) )
)

[Anonymous]

There is no error, but the the ans shows blank. The formula has no errors.

[Samarth_18]

Have you updated below condition in the formula as per your need?

FILTER ( AR_Data, ( AR_Data[Age Group - Discharge] = " 0-30" )

[Anonymous]

yes, i left the formula just the way it is, since i need that filter

[Samarth_18]

Hope you have verified, you have data for previous month specific date?

[Anonymous]

yes, when i checked i do have data for the previous/prior month

[Samarth_18]

Is it possible for you to share your PBIX file after removing confidential data?

[Anonymous]

how do i share the pbix file

[Samarth_18]

You can upload it in a drive and share the link.

[Anonymous]

I also have a field that is in the format of YYYYMM, Is there anyway that would work better

[Anonymous]

I dont think i can share that, it i going to take a lot of work to get rid of the confidential info.😟