How to show records within a date range as well as record with blanks in the date

[Jerush]

Hi All,

I am trying to do a report where I need to show all account a list of account and the sum of the values between a certain date range.. however I also need to list accounts that dont have values and subsequently do not have dates. 

I would preferably like to have the date slicer as between style so the user can choose the date range they need, but at the same time to also show "blank" dates... Is there any way this is possible.

Thank you.
Jerusha 

[Ashish_Mathur]

Hi,

In Power Query, ensure that the data type of the Date Ref column is Date.  Create a Calendar Table and build a relationship (Many to One and Single) from the Date ref column to the Date column of the Calendar Table.  To your slicer, drag Date from the Calendar Table and select any date range.  Write this measure

A = sum(Data[amount])

Hope this helps.

[Jerush]

Hi Ashish,
Thank you for the reply and solution. I will try it now and let you know.

[ryan_mayu]

@Jerush 

do you have measures? or you just display the table visual by date range selection?

[Jerush]

Hi Ryan,
I did not create any measure as yet... the the table has all the amounts and it will need to sum the amounts within the date range. I wont necessarily need to display the dates in the visual.. As long as the user know what dates they choose and can be seen on the slicer 🙂 

[ryan_mayu · Accepted solution]

@Jerush 

my suggestion is do not create relationship between date table and fact table. 

And create a measure

Measure = if(max('Table'[RefDate])>=min('date'[Date])&& max('Table'[RefDate])<=max('date'[Date]) || ISBLANK(max('Table'[RefDate])),1)

add this measure to visual filter and set to 1

pls see the attachment below

[Jerush]

Thanks so much Ryan.. this shows me all the data.. Im definitely on the right track now 

[ryan_mayu]

you are welcome