Forum Discussion

MKPartner's avatar
MKPartner
Helper II
9 months ago
Solved

Substracting data for proper visualization

Hi All,    I have data to calculate for next 4 weeks in one column. It's "lam_var" column. I'm trying to calculate value for each seprately but with simple SUM function I got only cumulative value ...
Need Help
  • danextian's avatar
    danextian
    9 months ago

    hi MKPartner 

    First get the number equivalent of your week (not wk42, 43) as you will have trouble comparing those weeks if week numbers aren't always in "00" format. For example Wk10 is next to Wk1 alphabetically. You should be able to get that in the query editor by duplicating your week colum and then replacing WK with nothing for the duplicate. Once you have that, create this measure:

    VAR _prevWk =
    CALCULATE (
        [sum of value],
        FILTER (
            ALL ( 'table'[wk number] ),
            'table'[wk number]
                = MAX ( 'table'[wk number] ) - 1
        )
    )
RETURN
    [sum of value] - _prevWk

     

    Otherwise please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

Shubham_rai955's avatar
Shubham_rai955
Super User
9 months ago

To show real weekly values instead of cumulative, calculate the difference between each week's value and the previous week:
WeeklyValue = CurrentWeekValue - PreviousWeekValue


Apply this for each prod_family and week; for the first week, use the value as-is. This way, your table shows only the actual value added that week.

MKPartner's avatar
MKPartner
Helper II
9 months ago

Thank you. I know how to it should work in theory but I have a problem to implment this with DAX code. There is screenshot from my data model if that helps. Data are for week 42 to 45 actualy. I'll polute data on daily basis so on Monday I would have data for weeks 43-46: