Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

VERY URGENT - MoM data with weekly data

amitchandak Please help.

I have data at weekly level and would need to calculate month on month variance for each week of the month with the corresponding week of the previous month. This is my DAX in Power BI. Please help me in getting the required result.

 

MoM_1_CF =

var sumoflastmonth =

 

CALCULATE(SUM('fred_h8_banking'[Amt in Bn_CF]), PREVIOUSMONTH('fred_h8_banking'[Week Rank_CF]),

ALLEXCEPT('fred_h8_banking',fred_h8_banking[series_title],'fred_h8_banking'[Month],fred_h8_banking[A/L],'fred_h8_banking'[banks]))

 

var sumofthismonth =

 

CALCULATE(SUM('fred_h8_banking'[Amt in Bn_CF]), ALLEXCEPT('fred_h8_banking',fred_h8_banking[series_title],'fred_h8_banking'[Week Rank_CF],'fred_h8_banking'[A/L],fred_h8_banking[banks]))

 

return DIVIDE((sumofthismonth-sumoflastmonth),sumoflastmonth,0)

 

//return divide (ABS(sumofthismonth -sumoflastmonth),sumoflastmonth,0)

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    1. Create columns with date.

    month = MONTH(Sheet1[date])
    weeknum = WEEKNUM(Sheet1[date],1)
    year = YEAR(Sheet1[date])

     

    2.Create a measure to calculate MoM data with weekly data.

    Measure =
VAR _1 =
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[weeknum] = SELECTEDVALUE ( Sheet1[weeknum] )
                && Sheet1[year] = SELECTEDVALUE ( Sheet1[weeknum] )
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[year] = SELECTEDVALUE ( Sheet1[year] )
                && Sheet1[weeknum]
                    = SELECTEDVALUE ( Sheet1[weeknum] ) - 4
        )
    )
RETURN
    _1 - _2

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    1. Create columns with date.

    month = MONTH(Sheet1[date])
    weeknum = WEEKNUM(Sheet1[date],1)
    year = YEAR(Sheet1[date])

     

    2.Create a measure to calculate MoM data with weekly data.

    Measure =
VAR _1 =
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[weeknum] = SELECTEDVALUE ( Sheet1[weeknum] )
                && Sheet1[year] = SELECTEDVALUE ( Sheet1[weeknum] )
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[year] = SELECTEDVALUE ( Sheet1[year] )
                && Sheet1[weeknum]
                    = SELECTEDVALUE ( Sheet1[weeknum] ) - 4
        )
    )
RETURN
    _1 - _2

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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