How to create date column in Calendar table that shows past dates

[Anonymous]

NOTE: the data is a DirectQuery, the calendar I built so I can add columns to it.

I can't figure out how to make a column of past dates only. I've posted before and a reply was that's not the way to do it. The last place I worked Microsoft BI consultants built out the framework including a massive calendar. The main column used from that was the column with only past dates in it. Yes, the consultants used that column. I am self trained in Desktop based on that calendar. 

The people that will be using the "cube" I am making have not seen Power BI or DAX. They will have to write measures!  

A column of past dates only makes building measures significantly easier. 

The issue today is it is the first of the month and we are reporting as of yesterday (last month). An automated report (relative filtered) is coulting this month for work days. 

Please help and thank you. I give KUDOS and mark Solved!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

AFAIK, most of dax function not allowed to use in calculated column/tables when you are work with directquery mode. I'd like to suggest you use t-sql query to direct add a custom column to compare current and result. (advanced option -> SQL statement)

Power Query SQL Server connector | Microsoft Docs 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example?

You can try a new column like this in the calendar table

if(Calendar[CalendarKey] < MAX(Fact_Sales[CalendarKey]), 1,0)

[Anonymous]

Hi @amitchandak ,

A calendar column with only past dates makes many measures in BI easier. Microsoft consultants made one at the last place I worked and that was the main column used by everyone including them.

I am just a self taught BI desktop person working part time. My directions are to make it easy for the people that will take my work over because they have not seen Power BI or DAX. 

We report prior day not current day

Today is a new month and some measures are looking to this month such as work days (even that has turned into a complicated problem as TODAY() -1 doesn't work in the DAX for some reason. 

The calendar with future dates makes writing past period measures harder when Month slicers are in the report

There has to be a way to make a column of only past dates. I have a marker if the CalendarDate <= sales[CalendarKey] =TRUE.  However, now they will have to use DAX filter functions and we both know filters and time functions are tricky.

Thank you much for your response. Hopefully you can help.