Prior month calculations (specific date range)

[snandy2011]

Hi all,

I am having trouble comparing current month with specific date range with previous month's same date range. I am able to calculate previous month calculaion by below DAX formula,

Prior Month Profit = CALCULATE(SUM( 'Poker Profit'[Gross Gaming Revenue] ),DATEADD(Dates[Date],-1,MONTH) )

Above formula will show previous month's total profit whatever month i will select into the slicer.But, the problem is that,current month is September and today is 19th and I want to show previous month's (i.e August) 1 to 19th August total profit. But, above formula is showing only august's total profit.

Let me clarify more my situation,

I have a month and week slicer.( week 1,week 2,week 3, and week 4).on the card visual i am showing   total profit , previous month total profit and other stuff.Now when i will select september on the month slicer it should show prior month (august) 1 to 19th total profit instead of whole month total profit. Again when i will select week 3 after choosing the spetember into the month slicer, it should show august's only 15th to 19th total profit, instead of august's week 3 total profit and It should be dynamic.That  means, on tomorrow it should show august's 1 to 20th total profit.

Expected output,

Month           Profit

July               500

August         600

September   300

Let suppose,August's 1 to 19th total profit is 400.. so, when i will select september on the slicer it should show prior month profit 400, instead of 600.again when i will select week 3 on the week slicer, it should show august 15th to 19th august total profit (lets say 150).

How can i solve this particular problem by DAX.

Please help me solvet this,

Any suggesation is really appreciable.

Thanks,

snandy

[v-frfei-msft · Accepted solution]

Hi @snandy2011,

I made one sample for your reference. You can refer to the steps as below to meet your requirement.

1. Enter the sample data and create a dimtime table.

dimtime = CALENDARAUTO()

Month = FORMAT(dimtime[Date],"mmm")

weekinmonth = CONCATENATE(dimtime[Month],"-week"&1 + WEEKNUM ( dimtime[Date] )-WEEKNUM( STARTOFMONTH (dimtime[Date])))

2. Create a measure as below.

Measure = 
VAR Year =
    YEAR ( MINX ( RELATEDTABLE ( 'Poker Profit' ), 'Poker Profit'[Date] ) )
VAR month =
    MONTH ( MINX ( RELATEDTABLE ( 'Poker Profit' ), 'Poker Profit'[Date] ) )
VAR day =
    DAY ( MINX ( RELATEDTABLE ( 'Poker Profit' ), 'Poker Profit'[Date] ) )
VAR may =
    YEAR ( MAXX ( RELATEDTABLE ( 'Poker Profit' ), 'Poker Profit'[Date] ) )
VAR mam =
    MONTH ( MAXX ( RELATEDTABLE ( 'Poker Profit' ), 'Poker Profit'[Date] ) )
VAR maxday =
    DAY ( MAXX ( RELATEDTABLE ( 'Poker Profit' ), 'Poker Profit'[Date] ) )
VAR todayweek =
    CONCATENATE (
        FORMAT ( TODAY (), "mmm" ),
        "-week"
            & 1 + WEEKNUM ( TODAY () )
                - WEEKNUM ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( dimtime[weekinmonth] ) = BLANK ()
            && SELECTEDVALUE ( dimtime[Month] ) = BLANK (),
        BLANK (),
        IF (
            FORMAT ( MAXX ( RELATEDTABLE ( 'Poker Profit' ), 'Poker Profit'[Date] ), "mmm" )
                = FORMAT ( TODAY (), "mmm" ),
            CALCULATE (
                SUM ( 'Poker Profit'[Gross Gaming Revenue] ),
                FILTER (
                    ALL ( dimtime ),
                    dimtime[Date]
                        >= DATE ( Year, month - 1, day )
                        && dimtime[Date]
                            <= DATE ( may, mam - 1, maxday )
                )
            ),
            CALCULATE (
                SUM ( 'Poker Profit'[Gross Gaming Revenue] ),
                DATEADD ( dimtime[Date], -1, MONTH )
            )
        )
    )

Then we can get the result as we excepted.

For more details, please check the pbix as attached.

Regards,

Frank

[snandy2011]

Hi @v-frfei-msft,

Absolutely 100 % worked your formula.You just made my day. I just omitted your weekinmonth conecpt.Beacuse i already made a week column in previous.And it worked as i expected.

We generally knew simple previous month concept.but never did, with specific date range. It will give us more compact analysis and comparison between current and previous month in terms of growth,profit margin etc. You just made it so simple.So, double kudos for that.

Thanks once again for your solution and reply.

Sincerely,

snandy