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

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper 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]
)