Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
speedramps
Super User
Super User

Reusing DAX code for retrospective reporting

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

 

1 REPLY 1
Greg_Deckler
Community Champion
Community Champion

Bit tough to follow. Generally to reuse measure code you wrap it in CALCULATE and provide a different filter. Can't exactly tell if that is what you are going for here. Another alternative might be the upcoming Calculation Group functionality.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.