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 to setup a needed calculation. I'm currently doing this by generating a table and adding a column called 'Date' which provides all of the dates between the sales month and sales month +12. It works fine as a 'table'...but I was wondering if it would work faster as a nested variable within a measure...in order to lower the size of my model and just store it as a temp table within the measure and call out only what I need. Below is the generate code...GENERATE( 'biplanning Sales_Fcst', FILTER( CALENDAR(MIN('biplanning Sales_Fcst'[Accounting Month]),MAX('biplanning Sales_Fcst'[Deferred Date])) ,[Date] >= 'biplanning Sales_Fcst'[Accounting Month] && [Date] <= [Deferred Date] && DAY([Date])=1 ))From this table...I'm creating a calculated column to get the correct counts.If possible...I'd rather create a measure if this will create efficiencies. Let me know what you think. Much appreciated!

Hi,What kind of measure are you trying to use this sales in? You are correct that you can create a variable e.g.Var _sales = DIVIDE([Sales],12)returnThen simply refer to this in your measure. However depending on the visulization an calculation goals you have you might need to add/remove filters to this variable.

ValtteriN The reason I have to spread a sale by month evenly is I have to take the 1st and last month divided by 24 and all months in between divided by 12. I use 'generate' to push the sales out 12months...and I divide the 1st/last month by 24 else 12. The sum of these columns is what I'm after.

Hi,I understand your goal a bit better now. Then a suggestion: you could place your GENERATE DAX within a variable and calculate e.g. SUMX of that.So something like this:Var _vartable =GENERATE( 'biplanning Sales_Fcst', FILTER( CALENDAR(MIN('biplanning Sales_Fcst'[Accounting Month]),MAX('biplanning Sales_Fcst'[Deferred Date])) ,[Date] >= 'biplanning Sales_Fcst'[Accounting Month] && [Date] <= [Deferred Date] && DAY([Date])=1 ))ReturnSUMX(_vartable,[calculated column])You can add the calculated column to the vartable using ADDCOLUMNS_vartable2 = ADDCOLUMNS(_vartable,"Calculated Column","Place your logic here")

ValtteriN Huge help!! I'm 'almost' there I think. I'm getting the correct total...but the 'dates' between Acct Date and Defferred Date in the 'generate statement' doesn't existing in my model...and that Date is what drives the breakout of the total in the measure. When actually generating the table it gave me 'date' to use. In the measure...I don't have 'date' available.

Hi,The logic with vartables is quite close to actual calculated tables. You can refer to the column in this variable table by using their names. So if you need the date in your calculate logic you can refer to it using 'Original table'[Date] reference. Here is an example: Vartable_example = var _vartable =ADDCOLUMNS(VartableExample,"Test",IF(VartableExample[Date]>=TODAY(),1,0))returnSUMX(_vartable,[Test])

Generate as a variable within a Measure vs Table Help

13 Replies

ValtteriN
Community Champion
4 years ago
Hi,

What kind of measure are you trying to use this sales in? You are correct that you can create a variable e.g.

Var _sales = DIVIDE([Sales],12)
return

Then simply refer to this in your measure. However depending on the visulization an calculation goals you have you might need to add/remove filters to this variable.
- codyraptor
  Resolver I
  4 years ago
  ValtteriN The reason I have to spread a sale by month evenly is I have to take the 1st and last month divided by 24 and all months in between divided by 12. I use 'generate' to push the sales out 12months...and I divide the 1st/last month by 24 else 12. The sum of these columns is what I'm after.
  - ValtteriN
    Community Champion
    4 years ago
    Hi,
    
    I understand your goal a bit better now. Then a suggestion: you could place your GENERATE DAX within a variable and calculate e.g. SUMX of that.
    
    So something like this:
    
    Var _vartable =
    GENERATE(
    'biplanning Sales_Fcst',
    FILTER(
    CALENDAR(MIN('biplanning Sales_Fcst'[Accounting Month]),MAX('biplanning Sales_Fcst'[Deferred Date]))
    ,[Date] >= 'biplanning Sales_Fcst'[Accounting Month] && [Date] <= [Deferred Date] && DAY([Date])=1
    )
    )
    Return
    
    SUMX(_vartable,[calculated column])
    
    You can add the calculated column to the vartable using ADDCOLUMNS
    
    _vartable2 =
    ADDCOLUMNS(_vartable,"Calculated Column","Place your logic here")
v-robertq-msft
Community Support
4 years ago
Hi,

Have you followed the DAX formula posted by AlexisOlson to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

Thanks in advance!

How to Get Your Question Answered Quickly

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- codyraptor
  Resolver I
  4 years ago
  The issue doesn't seem like it can be resolved by a measure...none of the suggestions work as I need to slice/filter by the dates that don't exist in the model. I think I'm limited to generating a table in order to do this. Please correct me if I'm wrong. Thanks!

Forum Discussion

Generate as a variable within a Measure vs Table Help

13 Replies

Related content

Variable Library Support in Notebook (Generally Available)

Generating Missing Data

Generating link

Variable Library

Lakehouse Table Generate Create Table