Cumulative total for Difference measure

Power BI
1

i have requirement

I have created currentmonthvalue on one facttable and previousmonthvalue on another facttable(this facttable is replicate of first facttable) and then i took difference amount measure.

MaterialClassificationslice
Excess
Old
New

Upon selection of above slicer for betwen 2 periods below table values are displayed
Now i need to get cumulative % for below table values.

Matid CuroMth Curtmonthval prevmonth prevmonval Diff Cumuative%
1 Jan2020 100 Dec2019 50 50 value
2 jan2020 150 Nov2019 70 80 value

Kindly help me on this

2

any reason why you created the 2nd fact table? Did you consider calculating previous month sale using

=
CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('Date'[Date]))



You can calculate cumulative sales using something like this

Cumulative Current Month Sales:=
CALCULATE (
	SUM(InternetSales_USD[SalesAmount_USD]),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

the diff cumulative % then could be a simple Divide function

would it be possible for you to share your data model or a pbix file if the above information does not work for you?

thank you

3

two fact tables i am using because i have only one monthid column i am using for currentmonth and comparemonth comparisons.

So Facttable1 has
monthid value classification materialid
1 100 new 1
2 50 old 1

simlilartly another fact tabe has same vaues to find out comparemonth slicer.

I have two measures
SelectmonthValue = sum(facttable[value])
Comparemonthbalue = SUMX(FILTER(facttable2,
and(facttable2[monthid]=previousdatemonthanalysis,facttabl2[materiaid]=currmateirla])),facttable2[value])
previousdatemonthanalysis = selectedvalue[dimdate2[monthid]
currmateirla = slectedvalue[fact1[materialid]

so then i calculated diff from above 2 measure
diff = SelectmonthValue -Comparemonthbalue

now based on diff cumulative totlas has to calculate for each clasiificaion selcted in slicer

kindly do needful

4

can you please attach a pbix file?

1 Like
5

Thanks for posting your question @amruthdna2018. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

6

Newtesting_11MayWithdimdateCalendar.pbix (154.2 KB)

NewDataSet.xlsx (11.9 KB)

I am not able to find cumulative totals for Diff Column kindly do needful

7

I have shared my pbix and sample dataset did you get any idea on implmentation part

8

i have tried to get you started. You definitely do not need 2 fact tables. .so i deleted one of them.
In your fact table you do not have dates. so based on your month index i tried to arrive at the trandate using the date function.,
i set up the CalendardDate as your Date table. You absolutely need a Date table for what you are trying to achieve.
I set up the DAX code to help you calculate running total.

I am attaching the pbix file that i worked on. Pls review and build on it . let me know if you have any further question.

Newtesting_11MayWithdimdateCalendar.pbix (113.8 KB)

9

hi thanks for quick reply.

When i add material name to the table cumulative totals are not workng as expected

like 2 materials 2 rows same Trandateā€¦cumulative totals are not working

kindly help me on this

10

pls send your latest pbix

11

Newtesting_11MayWithdimdateCalendar_Updated.pbix (114.6 KB)

Please find attacheed pbix