Substracting subtotal from subsequent subtotals (DAX formula)

[aldpbi]

Hi everyone, 

 I've been struggling lately to find a way to calculate the Delta column in PBI using DAX, as shown in below output example:

 

Just keep in mind the data is part of a larger model, and I need to retain the full flexibility of slicing the data.

Table structure example there this data:

Calendar Week	Factory	Category	…	Stock (past)

Could maybe more experienced help me with a hint in this endeavour?

Thanks a lot,

Cristian

[v-yuta-msft]

Hi aldpbi,

To achieve your requirement, create a calculate column using DAX formula below:

Delta = 
IF(ISBLANK(Table1[Stock(future)]), BLANK(), ABS(Table1[Stock(future)] - MAX(Table1[Stock(past)])))

 

Regards,

Jimmy Tao

[aldpbi]

Hi @v-yuta-msft,

Thanks so much for looking into it.

However, I don't think the calculated column does the job (see below screenshot). I was able to move closer to the solution by using this calculated measure:

Delta_measure = 
var maxweek = calculate(max('Table'[Calendar Week]),all('Table'),ISBLANK('Table'[Stock Past])=FALSE())
var laststock = calculate(sum('Table'[Stock Past]),'Table'[Calendar Week]=maxweek)

RETURN
ABS(laststock-sum('Table'[Stock Future]))

I still need a way to eliminate the past values' calculation when adding in a the table context:

Any Idea how I can achieve that? I would need to show blanks in above measure, where there's no future data.

I have added the PBX file with the calculation sample, for better context.

https://drive.google.com/file/d/1xrqMdHcmlbr3AzQ-eGi2LPUQ5o83YANF/view?usp=sharing

(hosted on g.drive - not aware of a better way to share the file on the platform directly)

Thanks a lot,

Cristian

[aldpbi · Accepted solution]

Hi all,

In case anyone interested, I was able to crack the puzzle with this measure:

Delta_measure = 
var maxweek = calculate(max('Table'[Calendar Week]),all('Table'),ISBLANK('Table'[Stock Past])=FALSE())
var laststock = calculate(sum('Table'[Stock Past]),'Table'[Calendar Week]=maxweek)

RETURN
if(LASTNONBLANK('Table'[Stock Future],'Table'[Stock Future]),ABS(laststock - sum('Table'[Stock Future])))

It is still not very clear why only the above syntax works, compared with any of these:

calculate(ABS(laststock - sum('Table'[Stock Future])),LASTNONBLANK('Table'[Stock Future],'Table'[Stock Future]))

calculate(ABS(laststock - sum('Table'[Stock Future])),'Table'[Stock Future]<>BLANK())

Cheers,

Cristian