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,

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

     



    • 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.

       

       

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        4 years ago

        I see,

        Now your measure is using current row date as the MIN date. We should able to solve this by using ALL. 


        e.g. Here I force the table in my previous example to use 1.1.2021 (MIN date) to test for SUMX

        Vartable_example =
        var _mindate = CALCULATE(MIN(VartableExample[Date]),ALL(VartableExample))
        var _vartable =
        ADDCOLUMNS(
         
        FILTER(ALL(VartableExample),VartableExample[Date] = _mindate ),"Test",IF(VartableExample[Date]>=TODAY(),1,0))
        return


        SUMX(_vartable,[Test])