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

DAX DAX Calculations
1

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])

RETURN

AddYEAR

Thanks,
Harry

2

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

3

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] )
RETURN
IF ( ISBLANK ( TotalSales ), 0, TotalSales )

Thanks
Ankit Jain

4

Hi @ankit,

Why below expression (non commented one) is not working

Sales Amount =

/*VAR TotalSales =

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

RETURN

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

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

Thanks,
Harry

5

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.

Thanks
Ankit

6

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))

Regards,
Harry

7

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

Cheers!!,
Harry

8

Based on the provided M Query, it appears that you are creating a date table called “Date” using the CALENDAR function. This table spans from January 1, 2014, to the current date. The table includes various columns such as “Month Name,” “Month Int,” “WeekDayINT,” “WeekDay,” “ISWeekend,” “YEARINT,” “Year,” “YearMonth,” and “YEARMONTHINT,” which provide additional information about each date.

If you are using the LinkedIn extension for Microsoft Excel, you can leverage its features to enhance your data analysis and visualization. Once you have excluded the sales data for June 30, 2014, from your main query and click “Close and Apply,” the visual representation of your data, including any charts, graphs, or tables, will be updated accordingly. The excluded date, in this case, June 30, 2014, will be removed from the visual representation to reflect the changes made in the query.

If you have any further questions or need assistance with the LinkedIn extension or any other topic, please let me know.