Forum Discussion
Count dates with no values
Hi there Fapo
Here's the general idea, where I'm calling your date table 'Date' and your fact table 'Sales':
With this you can get the number of dates that appear on the 'Sales' table:
A=COUNTROWS('Date'; Sales)
and with this the number of dates in the 'Date' table
B='COUNTROWS('Date')
then A-B would be the number of days that do not appear in 'Sales'. If you are familiar with the concept of expanded tables, that's what we are using in A. This assumes a relationship between 'Date' and 'Sales' of course.
From there you can apply slicers to detemine the period, client, etc.
Does that help?
Hi,
Mine has to be written as a calculated column formula (not as a measure).
I thought I made a column.
Is this not the correct way?
No, it is not. Click on the Data tab icon in the extreme right hand side vertical panel. Click on the Table in which you want to write the formula and then click on New column.
Oh ok, that was what I had done in the first image, it is not a measure. I clicked on New Column and in the formula bar wrote the following:
Gap Days Calc = IF(ISBLANK(CALCULATE(MAX('Transaction Header History'[Business Day]),FILTER('Transaction Header History','Transaction Header History'[Business Day]<EARLIER('Transaction Header History'[Business Day])))),BLANK(),1*('Transaction Header History'[Business Day]-CALCULATE(MAX('Transaction Header History'[Business Day]),FILTER('Transaction Header History','Transaction Header History'[Business Day]<EARLIER('Transaction Header History'[Business Day])))-1))