Count of number of transactions per region, per day

[G_Whit-UK]

Hi,

I'm working in Power BI and need some help with a measure to calculate the number of transactions per day, per region.  The data set has two key tables ("Date" and "Global Failing Sales Data").  The simplified table structure is as follows:

Date Table:

Date

01 April 2023

02 April 2023

03 April 2023

04 April 2023

05 April 2023

06 April 2023

07 April 2023

08 April 2023

09 April 2023

Global Failing Sales Data:

Date	Region	Trade Ref
01 April 2023	EMEA	12345
01 April 2023	EMEA	ABC123
01 April 2023	Americas	ABCDE
02 April 2023	EMEA	12345
02 April 2023	Americas	XYZ
04 April 2023	EMEA	9872
08 April 2023	Americas	CHT123
08 April 2023	Americas	NVC456
08 April 2023	Americas	JKD765
08 April 2023	EMEA	xd1562

The "Dates" has a date for every day within the reporting period (& beyond).  Therefore this is the best source for the dates.  The "Global Failing Sales" table only has records for dates which has activity.  Therefore there are some gaps in the date range.

Example of current DAX formulae:

Americas Failing Sales = 
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'Global Failing Sales Data'[Trade Ref] ),
    'Global Failing Sales Data'[Region] = "Americas"
)

The problem I need to solve is that the above DAX formula doesn't return a zero value for dates which do not have any failing sales data (due to gaps in the Global Failing Sales date range).  Ultimately I want to be able to create a chart to be something like the following (based on the above dummy data):

What is the correct measure which uses the dates from the Dates table, and the transaction count from the Global Failing Sales table?

(FYI:  It doesn't help if I amend the existing formula to "DISTINCTCOUNT")

[mw72]

Hi G_Whit_UK,

Could a simple solution be to

1) right click on the Date field and choose to 'Show items with no data'.

2) use a bar chart instead of a line chart?

[Ajendra]

Hello,  Try This!

Measure =

VAR _Date = MAX(DateMaster[Date])

VAR _Result  = COUNTROWS(FILTER(FactTable,FactTable[Date]<=_Date))

return

if(_Result <1,0,_Result)

[johnt75]

There's a couple of ways of tackling this. The simplest way is to simply add 0 to your existing measure, but that will return 0 for every date in your date table, even dates before the first ever transaction and after the last ever transaction. You might be able to work around that by putting filters onto the charts, or you might want to only return 0 for dates where it was possible for there to be a transaction, in which case check out https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax/ 

[G_Whit-UK]

Thanks @johnt75 for the suggestion.  I was thinking along the same lines, but was wondering if there is a smarter way of achieving this.  Do you know if there is a way in which I can add the "missing" dates (gaps) into the Global Failing Sales table by somehow importing dates from the Dates table?

[johnt75]

Don't think I'd recommend that, I think it would be a lot messier than the other options. You'd have to do something along the lines of getting all date-region combinations from your data and comparing that to a crossjoin of all possible date-region combinations. Then for every combination that is missing you would need to generate a row whose structure exactly matches the data table and finally union the generated table with the data. And if there were additional dimensions underneath region, e.g. state, then you'd need to include those as well.

[G_Whit-UK · Accepted solution]

Thanks again @johnt75 for your input.  The link you suggested in your original reply pointed me in the correct direction.  The issue was resolved by amending the DAX to be as follows:

Americas Failing Sales = 
VAR Raw_Count =
    CALCULATE (
        DISTINCTCOUNT ( 'Global Failing Sales Data'[Trade Ref] ),
        'Global Failing Sales Data'[Region] = "Americas"
    )
VAR Adj_Count = Raw_Count + 0
RETURN
    Adj_Count