Compare Month, day by day / display problem when number of day by month is different

[SylBell01]

Hi,

I have a dashbord table with a daily comparaison between two month. 

ex:  PrevMthByDay = CALCULATE([TotalRevenue],DATEADD(tbDate[Date],-1,MONTH))

If the current month is May (31 days) then it will be compare to April (30 days). In the dashboard display table, in theprevious month (april) column, the value is repeated.

ex :

Date           Current Month           Previous Month

May 30               1000$                      500$              (April 30)

May 31               2000$                      500$              (April 31 doesn't exist, the previous value is repeated) 

TOTAL                3000$                      500$              (the Total is good)

Would it be possible to have blank, zero or N/A ?

Thanks for you help

[jdbuchanan71]

Hello @SylBell01 

You can, I have a column in my Dates table that is the [Day of Month Number].  I can use that and compare the current day of month number to the prior month day of month number and if the current is > the prior show blank.

Prior Month Paid Amount = 
VAR _MonthDay =
    MAX ( DATES[Day of Month Number] )
VAR _PMMonthDay =
    CALCULATE (
        MAX ( DATES[Day of Month Number] ),
        DATEADD ( DATES[Date], -1, MONTH )
    )
RETURN
    IF (
        _MonthDay > _PMMonthDay,
        BLANK (),
        CALCULATE ( [paid amount], DATEADD ( DATES[Date], -1, MONTH ) )
    )

You can see the amount works for 3/28 but is blank for 3/29 - 3/31:

[jdbuchanan71]

If you don't want to mess with adding a column to your calendar table you can also do it using the DAY function and comparing those instead:

Prior Month Paid Amount = 
VAR _MonthDay =
    DAY ( SELECTEDVALUE ( DATES[Date]) )
VAR _PMMonthDay =
    CALCULATE (
        DAY ( SELECTEDVALUE ( DATES[Date]) ),
        DATEADD ( DATES[Date], -1, MONTH )
    )
RETURN
    IF (
        _MonthDay > _PMMonthDay,
        BLANK (),
        CALCULATE ( [paid amount], DATEADD ( DATES[Date], -1, MONTH ) )
    )

[SylBell01]

Thanks @jdbuchanan71 

It works perfectly in the table line but the total does'nt show.

[jdbuchanan71 · Accepted solution]

Interesting, that just means that the month you selected has more day than the prvious month so the total row is getting trapped as well.  We can fix it by only doing the check when we are looking at a single date value like so:

Prior Month Paid Amount = 
VAR _MonthDay =
    DAY ( SELECTEDVALUE ( DATES[Date] ) )
VAR _PMMonthDay =
    CALCULATE (
        DAY ( SELECTEDVALUE ( DATES[Date] ) ),
        DATEADD ( DATES[Date], -1, MONTH )
    )
RETURN
    IF (
        _MonthDay > _PMMonthDay && HASONEFILTER(DATES[Date]),
        BLANK (),
        CALCULATE ( [paid amount], DATEADD ( DATES[Date], -1, MONTH ) )
    )

The addition of the && HASONEFILTER(DATES[Date]) in the IF only returns BLANK() if we are on a single day line.

[SylBell01]

Wow it works perfectly. Thank you very much.

Would it be possible to ask you another question related to a similar problem ?

[jdbuchanan71]

You should post it as a new topic that way it gets more people to look at it.  Someone besides me may have a better answer for your next question.