Forum Discussion

codyraptor's avatar
codyraptor
Resolver I
4 years ago

Generate as a variable within a Measure vs Table Help

Hey all, Need help with a more efficient solution.  I'm taking 'sales' and spreading them from the sales date by 12months.  Meaning..the same sales number is spread evenly across 12months in order t...
calculated table
Generate
measure
variable
codyraptor's avatar
codyraptor
Resolver I
4 years ago

AlexisOlson Thanks.  I have a date dimension table based on the sales date.  Unfortunately...I don't have the 'spread date' in the underlying data...so there's no 'join' between the generated date in the temp table and the model.  I can do that if I stick with the generated table rather than the measure...but trying to make the model more efficient.  Are you familiar with how to pass a date filter through a temp table?   I have it working for individual selected dates...but not where it'll automatically recognize multiple months

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

I'm suggesting something along these lines:

 

SUMX (
    FILTER (
        DATESBETWEEN (
            dimDate[Date],
            MIN ( 'biplanning Sales_Fcst'[Accounting Month] ),
            MAX ( 'biplanning Sales_Fcst'[Deferred Date] )
        ),
        DAY ( [Date] ) = 1
    ),
    [Forecast Measure]
)