CODEWITHSUNDEEP
powerbidax
Trubs
Trubs

Reputation: 3032

Power BI (DAX) Show 0 for empty rows filtered on date

I'm attempting to show the sum of sales for EACH of the past 12 months, but the sales data is missing values for some months. I want these to show as 0

Data (which is linked to a DimDate Table)

SaleDate,Amount
20190101,25
20180201,30
20180301,12
20180501,10
20180502,5
20180701,60

enter image description here

The dax is pretty simple 

Failed Measure = 
VAR SelectedMonth = 201808
VAR SameMonthLastYear = SelectedMonth - 100
RETURN
    CALCULATE (
        Sum(sales[Amount]),
        DimDate[MonthInt] >= SameMonthLastYear,
        DimDate[MonthInt] <= SelectedMonth
    )

the SelectedMonth above comes from a filter selected by the user, which is just simplified for posting to so.

So the dax does work but the resultant table will only show me months where data exists in the sales table.

if this is all I wanted I wouldn't need dax

If I add a 0 to the Sum(Sales[Amount]) it shows all blank months as 0 and shows all months in the DimDate table. which is kind-a half way there, but at that point i need to filter the dates to only show the relevant 12 months.

close - but needs filtering

So the above should only show data from Aug 2018 to Aug 2017

It seems like it should be easy or a common problem, but I've had no luck finding a solution.

Upvotes: 0

Views: 7287

Answers (1)

RADO
RADO

Reputation: 8148

Try:

Hopefully Correct Measure =
VAR SelectedMonth = 201808
VAR SameMonthLastYear = SelectedMonth - 100
RETURN
    SUMX ( VALUES ( 'Date'[MonthInt] ),
           IF ('Date'[MonthInt] >= SameMonthLastYear && 'Date'[MonthInt] <= SelectedMonth,
           SUM ( Sales[Amount] ) + 0 )
    )

How it works: The problem with your "failed measure" (with zero) is that DAX always calculates it for all MonthYearShort items. Filtering is applied to calculation inputs, not to the items on the pivot rows. If calculation result is blank, then it's hidden by default. If you add zero to the results, then they are no longer blank and will show.

To solve the problem, you need to avoid calculating amounts for irrelevant months at all. There are several ways to do it; in my approach, I used SUMX to iterate over a list of MonthInt, test if it belongs to the desired time period, and if yes, calculate the sale amount, otherwise leave it blank (by default).

Upvotes: 1

Related Questions