Hi all,
I would like to know the number of valid prices for a day between two dates.
I am after the distinct number per material, per currency, on any specific date and for example for the last day of each month by year.
I can do this by expanding the individual material date ranges in as a separate table in either DAX or in Power Query (DAX code mentioned below).
However, my actual data would count for about 45.000 rows for each year, for 5+ years and this way it will create a rather large table.
I therefore aim to have the same output by using a measure, but I can’t wrap my head around how to do that.
If any of you could nudge me in the right direction, that would be most appreciated.
Note that I assume this can be done by using a measure - though I do not know. If a measure is not the best approach, I trust a best practice would be to create this table in Power Query to avoid using too much memory on this one table.
I have added the pbix and excel file with the data used in the example below.
Note that this pbix does not have a separate custom date table as it is only created for the purpose as a demo file for this challenge.
An example of my data is like the one below, with a ‘valid from’ and ‘valid to’ date.
I have also added an example of the expected output (for currency EUR).
Example of data
Expected output
DAX code for expanding the valid dates
Table =
VAR ExpandedTable =
GENERATE(
CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),
FILTER(
Sheet5,
[Date]<=Sheet5[Valid to] &&
[Date]>=Sheet5[Valid From]
//[Date]< IF(ISBLANK(SalesPriceChangesTransformation[Valid From]),TODAY(),SalesPriceChangesTransformation[Valid From])
)
)
RETURN
SUMMARIZE(
ExpandedTable,
[Date] ,
Sheet5[Material] ,
Sheet5[Currency] ,
“Count”,COUNTROWS(
SUMMARIZE(
Sheet5,
Sheet5[Material])
) )
price list demo.pbix (99.8 KB)
valid price test.xlsx (1.3 MB)
