Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Getting wrong project dates when generating table...

Hello PBI community:   I'm tracking revenue for projects over a few months; some projects generate revenue, and others stop (i.e., not cumulative total).    I have a table that has one row for ea...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    Please check if below table matches your request.

    Table = 
FILTER (
    GENERATE (
        SELECTCOLUMNS ( 'My Date Table', "My Date", 'My Date Table'[Date] ),
        Projects
    ),
    Projects[Est Start Date] <= [My Date]
        && Projects[Est End Date] >= [My Date]
        && DAY ( [My Date] ) = 1
)

    Regards,

    Cherie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

For those who are curious, the 'temporary' table returns a number labeled [Value]. It seems to correspond to the month number. The fix is changing the expression associated with the 'Month' column in the table generated by SELECTCOLUMNS. I set the expression to this:

 

"Month", DATE(YEAR([Est Start Date]),[Value],1),
 
That gave me the appropriate date value for my rows. 
 
**You have to use [value] in the calculation as that is how the dates differ from line to line.**
 
If you don't use [Value], the table will apply the same date under the month column to each row. [Value] is the key to giving each row for a project (that spans more than 1 month) a different date.