How do I add a calendar column that will have only past dates?

[Anonymous]

I've added many columns to the calendar using the DAX via "New Column".  I can't figure out how to make a column with past dates (ending yesterday) to simplify YTD, PRIOR YTD measures. The reports have month and year filters on them and that column would allow PREVIOUSYEAR() to be used for prior year. PREVIOUSYEAR() returns the entire year due to the calendar having future dates. 

I made a column BI_Calendar[Date_Sales] that contains TRUE the formula being:

Date_Sales = 'BI_Calendar'[Date] <= MAX(FACT_Sales[CalendarKey] ) 

I couldn't figure out how to use that to get previousYTD. Maybe that can be used to make a new calendar column.

Thank you in advance. I GIVE KUDOS and mark solved when solved!

[edhans · Accepted solution]

Hi @Anonymous - don't truncate your date table. See this article by SQLBI on hiding future dates in measures. It involves a very simple "Is Future" type column to filter your date table on. The crux of it is a measure like this:

Sales YTD hide v1 =
CALCULATE (
    [Sales Amount],
    CALCULATETABLE (
        DATESYTD ( 'Date'[Date] ),
        'Date'[DatesWithSales] = TRUE
    )
)

You would include that CALCULATETABLE() in your CALCULATE() function, then it couldn't see any future dates. Note that your model must refresh daily or the IsFuture column will get stale and report incorrect results until refreshed again.

[Anonymous]

@edhans ,

I keep running into problems due to the many different look back periods and requests for Month slicers. 

The last place I worked they had a Microsoft team build out the cubes and they had a date field with only past dates (closed accounting dates) and that was the field used by everyone for reports.

The newest problem that could be solved with a new date column of only past dates is accounting: Today is October 1 (22 work days), we are reporting September (21 work days). 

If I have a column of only past dates the month would still be September. I tried to compensate with the below but it doesn't work:

working_days =

CALCULATE ( COUNTROWS ( BI_Calendar ),

FILTER ( BI_Calendar,

AND( BI_Calendar[Month] = MONTH( TODAY () -1 ) ,

not WEEKDAY( BI_Calendar[Day of Week]) in {1,7}

) ))

Can you help with creating a column of past dates please and thank you with KUDOS!

[Anonymous]

Thank you! KUDOS! 

[edhans]

Glad to assist @Anonymous 

[Anonymous]

Thank you except, I can get YTD. I need Prior YTD. 

SAMEPERIODLASTYEUR() doesn't ignore the time slicer in the report

PREVIOUSYEAR() returns the total prior year. I tried filtering it but that didn't work (here is that fail)

Sales_USD_YTDPrior =

CALCULATE(FACT_Sales[Sales_USD_Net_Dis],

PREVIOUSYEAR( BI_Calendar[Date]),

BI_Calendar[Date_Sales] = TRUE ) ( I was hoping this filtered the current year but it did not)

The column BI_Calendar[Date_Sales] I made and it is TRUE for a date with a sale. But, all the prior year is TRUE. 

I thought simply adding a column with only past dates (Closed_Dates) was the easiest and cleanest.