How To get sales for date as zero when no fact is available?

For e.g.
If i exclude 6/30/2014 sales from M Query and do close and apply.

After Checking Total Sales for each day the date excluded in gone in visual.

FYI - Date table in model is prepared by Below query and marked as Date Table post same

> Date =
VAR BaseTable = CALENDAR("2014-1-1",TODAY())

VAR AddMonth = ADDCOLUMNS(BaseTable,"Month Name",FORMAT([Date],"MMMM"),"Month Int",MONTH([Date]),"WeekDayINT",WEEKDAY([Date],3),"WeekDay",FORMAT([Date],"dddd"),"ISWeekend",IF(WEEKDAY([Date],3)<5 ,1,0))

VAR AddYEAR = ADDCOLUMNS(AddMonth,"YEARINT",YEAR([Date]),"Year",FORMAT([Date],"YYYY"),"YearMonth", FORMAT([Date],"YYYY-MMM"),"YEARMONTHINT",YEAR([Date])*1000+[Month Int])




Please see if “show items with no data” resolves your issue. Here’s a link on how to do that.

Hi @HarsimranjeetSingh - In case you are OK to show value as Blank for missing dates, please use “Show Items with No Data” as @Melissa mentioned.

However to show value as ‘0’ for missing dates, you can create a new measure like below.
SalesAmount_Blank =
VAR TotalSales =
SUM ( FactInternetSales[SalesAmount] )
IF ( ISBLANK ( TotalSales ), 0, TotalSales )

Ankit Jain

Hi @ankit,

Why below expression (non commented one) is not working

Sales Amount =

/*VAR TotalSales =

SUMX (Sales,Sales[Quantity] * Sales[Unit Price] )


IF ( ISBLANK ( TotalSales ), 0, TotalSales ) */

SUMX ( Sales, IF (ISBLANK(Sales[Quantity] * Sales[Unit Price] ),0,Sales[Quantity] * Sales[Unit Price] ))


Hi @HarsimranjeetSingh.

Measures work on aggregated value. In Commented, we are checking if sum of all rows (Aggregated value) for particular date is blank, if yes, then ‘0’ else Fact value.

In other expression, you are trying to check IsBlank() for entire Sales table across multiple rows, which is not possible for a measure.


Isn’t the other expression will be working for particular date.
I believe If I’ve selected only one Visual, where Date and this measure is selected it will work in following manner.

  1. First the Evaluation Context which will be lets say first date in my visual

  2. Then ,Fact table will filter for that date using active relationship.

  3. Then, Iterator will work on selected rows from step 2 row by row

So if there are no rows for excluded date 6/30/2014 and evaluation context is for this date sum of all rows should be blank.

Correct me if I’m missing some thing here.

I came to above points for iterator w.r.t to below expression
SUMX(Sales_Data,IF(Sales_Data[Channel]=“Export”,Sales_Data[Total Revenue],0))


Thanks @ankit , @Melissa for your suggestion.
Opening another thread for pending doubts.