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
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])
  • codyraptor's avatar
    codyraptor
    Resolver 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.