Conditional monthly cumulative totals based on filters

[Anonymous]

I'd like to show cumulative totals for a period based on certain filters. I am able to do it with SQL but having trouble to do the same using Power BI.

I've two tables - actual data (A) and (B) calendar table (with start and end dates). So, with SQL i just joined these tables using the following join condition; this basically duplicates data for every unit each month which i can then use for aggregations etc in Power BI

SELECT * FROM A 

LEFT JOIN B

ON A.InstallDate <= B.EndDate

WHERE Status IN ('Live','Pending Disconnect')

In Power BI, i tried to used power query to merge these tables but cannot use the join condition the same way. I have also tried DAX Calculate and Filter but had no luck

Attached is sample data and expected output. I could not attach sexcel file, so included a screenshot

I am hoping to get some guidance here

Thank You

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

Excatly what i have been looking for. Thank You!!

I am now trying to understand the steps you have in power query

Lot to learn for me 

[Ashish_Mathur]

You are welcome.

[Anonymous]

@Ashish_Mathur - If i may ask you another question about the same

i am trying to create a forecast for the previous and current years based on the distinct sample data and some other columns

A = SUM(Amount) -- for id's with pending disconnect in the Year

B = SUM(Amount) -- for id's that are live

C = SUM(Amount) -- for id's that have been disconnected in the year

D = SUM(UserCount)

ForecastA = ((A+B)-C)/D

Something like this.. So, i have created measures for A,B,C to verify the data and thought i would be able to use these measures in final calculation but had no luck. Can we not use measures along with columns from other related tables (d is from different table)?

I was able to use Variables and do the calculations but i have to rewrite the same piece of code for few different measures i.e., (A+B)-C calculation would be same but D column would be different.

Is there any simpler way to do this?

Thanks

Measure = 
	VAR AmountLive = CALCULATE(SUM(LD[Amount]),FILTER(LD,LD[Status]="Live"))
	VAR AmountOnOrder = CALCULATE(SUM(LD[Amount]),FILTER(LD,AND(LD[Status]="On Order",LD[PIyear] = Year(Now()))))
	VAR AmountPD = CALCULATE(SUM(LD[Amount]),FILTER(LD,AND(LD[Status]="Pend Disc",LD[PDYear] = Year(Now()))))
	VAR TotalAmt = SUM((AmountLive+AmountOnOrder)-AmountPD)
	VAR TotalHC = SUM(BB[Users])

RETURN DIVIDE(TotalAmt, TotalHC)

[Ashish_Mathur]

Hi,

I just do not understand your question.  Show a dataset, explain the business problem and show me the expected result.

[Abduvali]

Hi @Anonymous,

Not sure if you tried creating a calendar table?! If not, createa calendar table:

DAX:

Date = ADDCOLUMNS ( CALENDAR (DATE(2017,1,1), DATE(2020,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

Then create a relationship between calendar date field and your tables based on their date fields (install date and end date).

Then try creating a calculated column or measure to sum amounts.

something like this: calculate(sum('table name' [amount]), 'table name'[status] = "Live'', 'table name'[status] = ''Pending Disconnect'')

But use date field from the calendar table in your table matrix.

Hope it helps.

Regards

Abduvali

[Anonymous]

Thanks for your reply. I've tried something similar and it did not work.

I've tried it again using the sample data and the DAX function you mentioned. i got no value. I tried the following

 and it does give me cumulative values but not only for the 3 months, that i have data for

SampleMeasure = Calculate(Sum('Sample Data'[Amount]), FILTER(ALL('Sample Data'), 'Sample Data'[Install Date] <= MAX(DateCalendar[Date])))