How to do a balance and beginning count for each month ?

[PBI5851]

nHello,

Below is the raw data i have 

RawCount	keydate	KeyField
3	11/30/2020	New Shoes
3	12/31/2020	New Shoes
-1	12/31/2020	Discontinued
-1	1/31/2021	Discontinued
-1	2/28/2021	Discontinued

And i hope to achieve the below. 

	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21
Beginning	0	3	5	4	3	3
New	3	3	0	0	0	0
Discontinued	0	1	1	1	0	0
Balance	3	5	4	3	3	3

But with the logic and dax i have, i am achieving only this. 

	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21
Beginning	0	3	5	4	0	0
New	3	3	0	0	0	0
Discontinued	0	1	1	1	0	0
Balance	3	5	4	3	0	0

The March and Apr beginning and balance are incorrect as they are zero's.  This is because of the "selected" variable as per below. 

Balance =
var selected = max(SummaryTable[KeyDate])
Return
CALCULATE(sum(SummaryTable[RawCount]), FILTER(all(SummaryTable), SummaryTable[KeyDate] <= selected))

Beginning =
var selected = max(SummaryTable[KeyDate])
Return
CALCULATE(sum(SummaryTable[RawCount]), FILTER(all(SummaryTable), SummaryTable[KeyDate] < selected))

If there is a value for every month in the raw table, i get correct results, but if there are none, then i get no results. Please can you advise on how to get the begin and balance numbers for those months that arent't part of the raw table. 

[Anonymous · Accepted solution]

Hi @PBI5851 ,

The solution to this situation is to create a calendar table,

Table 2 = ADDCOLUMNS(CALENDAR(DATE(2020,11,1),DATE(2021,4,30)),"Month-Year",FORMAT([Date],"MMM")&"-"&FORMAT([Date],"YY"),"sort",YEAR([Date])*100+MONTH([Date]))

Balance = 
var selected = max(SummaryTable[KeyDate])
Return
CALCULATE(sum(SummaryTable[RawCount]), FILTER(all(SummaryTable), SummaryTable[KeyDate] <= MAX('Table 2'[Date])))

Beginning = 
var selected = max(SummaryTable[KeyDate])
Return
CALCULATE(sum(SummaryTable[RawCount]), FILTER(all(SummaryTable), SummaryTable[KeyDate] < MAX('Table 2'[Date])))

 

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PBI5851 , Try with help from a date table

Balance =
CALCULATE(sum(SummaryTable[RawCount]), FILTER(all(Date), Date[Date] <= max(Date[Date])))

Beginning =
CALCULATE(sum(SummaryTable[RawCount]), FILTER(all(Date), Date[Date] < Min(Date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.