Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cost comparison

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

 

 

 

 

  • amitchandak's avatar
    amitchandak
    5 years ago

    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))

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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