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

Yep...I think I'm doing that in my variable.  My issue is 'Date' doesn't exist in my model...it only exists in the Generate statement.  So once I calculate the measure...I don't have anything in my model to break it out by the generated date.  Below is an example of the calculated table..vs the measure.  I need to sum by 'Date' once the ECC calc is created.

 

I tried a 'group by' and that didn't change anything.  I'm also trying a filter 'Date' = 'Accounting month'.  Thought that might get me the correct sum when the Accounting Date is chosen.  Neither seem to be changing the sum.

 

 

  • 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]
)