Table with all dates between last sales day and defined N periods

Question

How to get Dates for N last full months. I want the last month to be determined by Sales amount.

This is the whole table for the example.

The expected result is a calculated table of Date column from 2020-05-01 to 2020-07-31. Looks like this:

Date
2020-05-01
2020-05-02
2020-05-03
…
2020-07-29
2020-07-30
2020-07-31

What have I tried? First, a measure to get the last date with sales:

MaxDate = 
CALCULATE( 
    EOMONTH( MAX( T[Date] ), 0),
    ALL( T ),
    T[Amount] > 0 
)

And the calculated table:

T_Range = 
var a = [MaxDate]
var b = DATESINPERIOD( T[Date] , a, -3, MONTH )
return 
b

But it returns only 3 days, not the whole range from 2020-05-01 to 2020-07-31.

The table to reproduce the problem:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcrLCQAgDATRXnJWSNZ/LWL/bShIQFyY02PmFCg0qp0kiMkKTmBKTJmpROCjyldj6pceGb+YkhgJXNYG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}})
in
    #"Changed Type"

srinivasu srinivasu · Accepted Answer · 2020-04-15T20:24:29

Please check if this helps -

Created a new column with 'Max date' measure you provided.

Then crated min date column with below DAX.

Min Date = EDATE([MaxDate],-3)+1

Created new table using below.

T_Range = CALENDAR(MAX(T[Min Date]),MAX(T[Max Date]))

Table with all dates between last sales day and defined N periods

1 Answers