Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
theblueslover
Frequent Visitor

Running total not showing any value against blank months

One of the months has no data in my fact table and my running total measure works fine in terms of displaying the running total value in the blank month. However, I need to add a Max Transaction Date filter (highlighted below) from the Fact table as well to stop to curve at the last date.

And when I add this additonal filter, the running total measure shows blank value against that month.

Running Total =
var maxdate = Calculate(MAX(FactTable[Transaction_date]))
 
Return
 
SUMX(
FILTER(
ALLSELECTED('Calendar'[Date]),
'Calendar'[Date]<=MAX('Calendar'[Date])
&&
'Calendar'[date]<=maxdate
),
[1.0 Sum]
)
screenshot below is the result the running total measure returns - this happens when I add the additional max date of transaction from the fafct table.theblueslover_3-1652253435229.png

 


Any guidance is much appreciated.

 


2 REPLIES 2
v-eqin-msft
Community Support
Community Support

Hi @theblueslover ,

 

1.What's the syntax for [1.0 Sum]? I could reproduce your issue when the [1.0 Sum]=SUM(FactTable[Value]), and it show blank for 2021/12/1:

Eyelyn9_0-1652665522407.png

2.What's the expected result for 2021/12/1? Do you want to get the last not blank value for it? In my example , like:

Eyelyn9_2-1652665660277.png

If so , please try:

New Sum = 
var _date= IF([1.0 Sum]=BLANK(),MAXX(FILTER(ALLSELECTED(FactTable),[Transaction_date]<MAX('FactTable'[Transaction_date])),[Transaction_date]), MAX('FactTable'[Transaction_date]))
return SUMX(FILTER(ALL('FactTable'),[Transaction_date]=_date),[1.0 Sum])
Running Total 2 = 
var  maxdate = Calculate(MAX(FactTable[Transaction_date]))
return  SUMX(
FILTER(
ALLSELECTED('Calendar'[Date]),
'Calendar'[Date]<=MAX('Calendar'[Date])
&&
'Calendar'[date]<=maxdate
),
[New Sum]
)

Eyelyn9_3-1652665751835.png

Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

 

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

Thank you so much for the reply. I have tried to apply the measures but couldnt make it work.

 

What I want to do is to stop the curve at the last period in which there is transactions in the fact table.

theblueslover_0-1652682824620.png

 

 

When I add the maximum date from the fact table into the running total measure this is what I get.

theblueslover_1-1652683018380.png

 

"DimDate[Month]<=maxdate" is the filter that causes this.

 

 

Sales running total = 
var maxdate = MAX('Fact'[Dates])
return
CALCULATE(
	SUM('Fact'[Sales]),
	FILTER(
		ALLSELECTED('DimDate'[Month]),
		DimDate[Month]<=MAX(DimDate[Month]) && DimDate[Month]<=maxdate
        )
	)

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.