Substracting data for proper visualization

[MKPartner]

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 for each week like below.

Lam_Variance = ABS(SUM('Demand Schedule_Lam D'[lam_var]))

I'd like to see real value for each prod_family line and total for each week as well. I will have more data with next weeks but always for actual week and visibility for next 3 weeks.

Thanks for your support.

[grazitti_sapna]

@MKPartner  try this

Lam_Variance_Weekly =
VAR CurrentWeek =
SELECTEDVALUE('Demand Schedule_Lam D'[Week])
RETURN
CALCULATE(
ABS(SUM('Demand Schedule_Lam D'[lam_var])),
'Demand Schedule_Lam D'[Week] = CurrentWeek
)

[danextian · Accepted solution]

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]

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]

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: 

[grazitti_sapna]

Hi @MKPartner,

Use Below DAX

Lam_Variance_Weekly =
SUMX(
VALUES('Demand Schedule_Lam D'[Week]), -- or your week/date column
ABS(SUM('Demand Schedule_Lam D'[lam_var]))
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[MKPartner]

Thanks for feedback but it's not the result which I want to have. Values for each week didn't change. Only last column is different againt previous image.