Running total skipping months with no costs

[KLK]

I have a Running total Formula that is skipping months with no costs. 

**bleep** Actuals = CALCULATE(SUM('FY CJI3'[Actuals]), FILTER(ALLSELECTED('FY CJI3'),'FY CJI3'[Postg Date]<=MAXA('FY CJI3'[Postg Date]))).

This works fine unless the month has no costs then is skips, then when costs hit again it sums correctly. I have a Days [Date] column that links to my fy CJI3 [Postg Date] column for my actual costs. My dates table has Month, Fiscal Year, Accounting Period etc. I have tried many formulas and they do the same thing, skipping months. 

Running Total 2 =

var maxdate = Calculate(MAX('FY CJI3'[Postg Date]))

return

SUMX(

FILTER(

ALLSELECTED(Days[Date]),

Days[Date]<=MAX(Days[Date])

&&

Days[Date]<=maxdate

),

[Sum of Actuals 2]

).

I'm not sure what I'm doing wrong. Thank you in advance for any help.

[Ashish_Mathur]

Hi,

If you want running total for CY, then write this measure

RT = calculate([sum of actual 2],datesytd(calendar[date],"31/12"))

If you want running total since inception, then write this measure

RT = calculate([sum of actual 2],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Ensure that you drag Year and Month name from the Calendar Table to the visual.

[KLK]

This formula made the actuals diassapear. 

RT = calculate([Sum of Actuals 2],datesytd(Days[Date],"31/12"))

On the table it produced a bunch of repeating months with 0s all down the table. 

I'm wondering if it has something to do with the drilldown. I have several criteria. The NWA is the lowest level and that is the key from the Master table and also what is the lowest level of drilldown capability. I roll up by category and the bottom axis is by month. This graph is filtered to a single NWA, but the problem exists in the total as well. this NWA total drops out of the rollup total as well.

[KLK]

Sure! No problem,

Right now the actuals are:

The running total formula I'm using returns:

But I want them to fill in the blank months with the former month cumulative total like this:

I was trying to SummarizeCoulmns but that cannot return a scalar value for the graph. The graph is a picture of all with a slicer that can drill down to an independant NWA if you want. Everything is linked one to many. Days [Date] to FY CJI3 ]Postg Date] both formated Date/Time 03/14/2001 (mm/dd/yyyy). I just cannot get those numbers to reflect in months where no actuals costs exists. I'm so stuck. Thank you so much for the help!!

[Ashish_Mathur]

Share the download link of the PBI file.

[Ashish_Mathur]

Hi,

I cannot understand the question then.  It will help if you can share the download link of the PBI file and show the problem clearly.  Also, show the expected result in a Table.  Once we get the correct numbers there, we can always create the chart of our choice. 

[KLK]

Unfortunately, I cannot share the PBI file itself. It is very weird that this is the only formula not working. Only when I try to display the data cummulative by date does it do this particular error.

[amitchandak]

@KLK , You should join your date of table with the date of a date table and then try a measure like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[KLK]

My Days [Date] table has every day then the columns are Month, FY, MonthYR, Acct Per, Period Start and Period end etc. The date column is a one to many that links to the Postg Dat on my FY CJI3 fact table. As long as the month has costs the measure works and the cumulative total is correct. It is only when the month has no data whatsoever that it just skips the month.

Cumm Sales = CALCULATE(SUM('FY CJI3'[Actuals]),filter(all('Days'),'Days'[Date]<=max('Days'[Date]))) This produced repeating across.

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

What does the [NET] mean? Also what is the Window? I get the red lines. I have 2016 version of BI ATM I believe. It doesn't like the orderby in this either. Thank you!!!

[KLK]

Also, My 'Days' [Date] column is Date/Time 03/14/2001 (mm/dd/yyyy). As is my 'FY CJI3' [Postg Date]. However the funny part, the Period End Date and Period End Date columns all have a calendar next to them in the fields pane but my Days [Date] and my CJI3 [Postg Date] do not. But they are formated and look like they seem to be functioning like dates.