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
ValtteriN's avatar
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's avatar
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's avatar
    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")


    • codyraptor's avatar
      codyraptor
      Resolver I
      4 years ago

      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. 

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        4 years ago

        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))
        return

        SUMX(_vartable,[Test])