Cost comparison

[Anonymous]

Hi Experts,

Can any one please help me to arrive the soultion?

I have a month wise cost details for each application individually, now i want to compare the current month cost with previous month cost and take top 10 application groups and plot the graph. Every month, the cost will get change depens on the usage. 

Thanks,

saranya

[v-eqin-msft]

Hi @Anonymous ,

According to my understand, you want to calculate the difference between Current Month and Last Month,right?

I did it in two ways, you could take a look at  my pbix file here

Method1:

YearMonth =
YEAR ( [Date] ) * 100
    + MONTH ( [Date] )

differenceMethod1 =
VAR _app =
    SELECTEDVALUE ( 'Table'[Application] )
VAR _yearmonth =
    SELECTEDVALUE ( 'Table'[YearMonth] )
VAR _lastmonth =
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Application] = _app
                && 'Table'[YearMonth]
                    = CALCULATE (
                        MAX ( 'Table'[YearMonth] ),
                        FILTER ( ALL ( 'Table' ), 'Table'[YearMonth] < _yearmonth )
                    )
        )
    )
RETURN
    IF (
        _lastmonth = BLANK (),
        MAX ( 'Table'[Cost] ),
        CALCULATE ( SUM ( 'Table'[Cost] ) - _lastmonth )
)

My visualization looks like this:

Method2:

YearMonth =
YEAR ( 'Table1'[Date] ) * 100
+ MONTH ( 'Table1'[Date] )

DateSlicer =
ALLSELECTED ( Table1[YearMonth] )

differenceMethod2 =
VAR _sele =
    SELECTEDVALUE ( DateSlicer[YearMonth] )
VAR _diff =
    _sele - MAX ( 'Table1'[YearMonth] )
VAR _sum =
    IF ( _diff = 0 || _diff = 1 || _diff = 89, SUM ( 'Table1'[Cost] ), BLANK () )
VAR currMinusPre =
    CALCULATE (
        SUM ( 'Table1'[Cost] ),
        FILTER ( Table1, 'Table1'[YearMonth] = _sele )
    )
        - CALCULATE (
            SUM ( 'Table1'[Cost] ),
            FILTER (
                Table1,
                'Table1'[YearMonth] = _sele - 1
                    || 'Table1'[YearMonth] = _sele - 89
            )
        )
RETURN
    IF ( HASONEVALUE ( 'Table1'[YearMonth] ), _sum, currMinusPre )

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak · Accepted solution]

@Anonymous , if this the base table you need to unpivot. and convert month into number or date.

https://radacad.com/pivot-and-unpivot-with-power-bi

With a date or month date

//Date and date table

MTD Sales = CALCULATE(Average(Sales[Price]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(Average(Sales[Price]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

//Month no or rank

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
This Month = CALCULATE(Average('Sales'[Price]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(Average('Sales'[Price]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))