Showing results for 
Search instead for 
Did you mean: 
ronakvi14 Regular Visitor
Regular Visitor

cumulative skipping values if cy date sales are blank

sales CY vsLY.JPGI am calculating cumulative sales for the month selected 


Cumulative Amount CY = CALCULATE([Total Sales],
FSP[Date_dt]<= MAX(FSP[Date_dt] ) && YEAR(FSP[Date_dt])=YEAR(TODAY())))


Cumulative Amount PY = CALCULATE([total sales ly],
FSP[Date_dt]<= MAX(FSP[Date_dt] ) ))




when u look at this for CY its fetching correct values , but for LY it is skipping the values if CY data is blank 


eg. 10 nov 2018  is sunday and sales is blank 

but 10 nov 2017 has sales 332353.67 which is missing in the calculation of cumulative amountPY so the amount is not matching missing values.JPG


Calculated measure:

(total sales ly = CALCULATE(SUM(FSP[Sales]),SAMEPERIODLASTYEAR(FSP[Date_dt].[Date]),ALL(FSP)) )


please can you help me out 


Thanks in advance 

ronakvi14 Regular Visitor
Regular Visitor

Re: cumulative skipping values if cy date sales are blank

Can anyone please help

Moderator v-yuezhe-msft

Re: cumulative skipping values if cy date sales are blank


Please create a specific calendar table in Power BI Desktop following the guide in this blog, then replace the date fields in the above DAX formula to Date field of Calendar table. After that , drag date fields of Calendar table to your visuals and check if the result of your measure is correct.

If you still get incorrect result, please share sample data of your table that can by copied and pasted.


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.