Forum Discussion

codyraptor's avatar
codyraptor
Icon for Resolver I rankResolver 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
Icon for Resolver I rankResolver 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
Icon for Community Champion rankCommunity 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
    Icon for Resolver I rankResolver 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
      Icon for Community Champion rankCommunity 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])
      • codyraptor's avatar
        codyraptor
        Icon for Resolver I rankResolver I
        4 years ago

        ValtteriN I'm sorry...I don't think I follow.  Just to clarify a bit further...below is a visual of what I need.  The dates across the top are 'Acct dates' which are in the model.  The Dates in the rows are 'generated' in the table variable.  I need the sum of those rows...by Accounting Month.

         

      • codyraptor's avatar
        codyraptor
        Icon for Resolver I rankResolver 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