I want to reuse DAX code for retrospective reporting, rather than coy and paste it each time.
Let me explain .....
Some Fact tabes are SCF (Slow Changing Facts).
For example a row on the FactSales may be initially written on 1Feb2020
with a sales quantity of 5 but then be edited on 10Feb2000 to 6.
This would also impact the sales value, weight and postage.
Each row therefore has a valid from and to date, for example:-
Row id | Valid from | Valid to | Date of sale | Invoice id | Customer id | Product id | Quantity | Sales value | Weight | Postage |
1 | 01/02/2000 | 09/02/2000 | 01/02/2020 | 999 | 123 | 456 | 5 | 50.00 | 10 | 2.00 |
2 | 10/02/2000 | 31/12/9999 | 01/02/2020 | 999 | 123 | 456 | 6 | 60.00 | 12 | 3.00 |
| | | | | | | | | | |
It is possible to create a DAX measure to allow the user to pick and choose a date from a list of dates to see the total quantity, value, weight and postage that was retrospectively valid for that date. So 9Feb2020 will show 5 but 11Feb2020 will show 6. For example:-
Retrospective quantity =
--- get selected date
VAR retrospectivedate = MAX('List of dates'[Date])
--- create a subset of the table where the row valid from/to range covers the selected date
VAR facts_within_range =
FILTER(FactSales,
retrospectivedate >= FactSales[Valid from] &&
retrospectivedate <= FactSales[Valid to ]
)
Return
CALCULATE (
SUM(FactSales[Quantity]),
facts_within_range
)
It is good practice to define DAX code once and use it always, rather than copy and paste it. I would therefore like to put the top half of the code into a measure so I can easily reuse it and centrally maintain it. Rather than copy, paste and maintain it into the value, weight, postage and other measures.
For example
FactsWithinRange =
--- get selected date
VAR retrospectivedate = MAX('List of dates'[Date])
RETURN
--- create a subset of the table where the row valid from/to range covers the selected date
FILTER(FactSales,
retrospectivedate >= FactSales[Valid from] &&
retrospectivedate <= FactSales[Valid to ]
)
and then ......
Retrospective value =
CALCULATE (
SUM(FactSales[value]),
[FactsWithinRange]
)
Retrospective weight =
CALCULATE (
SUM(FactSales[weight]),
[FactsWithinRange]
)
Retrospective postage =
CALCULATE (
SUM(FactSales[postage]),
[FactsWithinRange]
)
Obviously I cant do this because:-
whist DAX allows VAR to store table variables like facts_within_range
DAX wont allow measure like [FactsWithinRange] because a DAX measure must be a scalar values.
Also whilst CALCULATE allows VAR variables like facts_within_rangein the expression,
it wont allow DAX measures in the expression.
Please can you suggest alternative ways I can reuse the DAX code and not have to copy and paste it? Thank you.
Note, I know I could use a what if parameter and query filter but this means the user will have to press refresh each time they pick and choose a date. I much prefer to use DAX so the user can just click a date from a list, and instantly see the retrospective data.
Many thanks
