Solved: Creating a Calendar Table with the First date base...

Ashoop2020 · ‎12-15-2022

Hello!

I am looking to create a calendar table that starts with the first date in the below table where the Print Drop Column = Yes, and the last date is the last date in the below table.

I know how to create the table I am just not sure how to implement a condition with the "FIRSTDATE" function.

Thanks!

v-yiruan-msft · ‎12-15-2022

Hi @Ashoop2020 ,

You can create a calculated table as below, please find the details in the attachment.

Calendar = 
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[date] ), 'Table'[Print Drop] = "Yes" )
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[date] ), 'Table'[Print Drop] = "Yes" )
RETURN
    CALENDAR ( _mindate, _maxdate )

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yiruan-msft · ‎12-15-2022

Hi @Ashoop2020 ,

You can create a calculated table as below, please find the details in the attachment.

Calendar = 
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[date] ), 'Table'[Print Drop] = "Yes" )
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[date] ), 'Table'[Print Drop] = "Yes" )
RETURN
    CALENDAR ( _mindate, _maxdate )

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

WinterMist · ‎12-15-2022

@Ashoop2020

For a sound Date Table, this is not recommended. For date-time functions to work, DAX is very picky and demands that the date table begin on January 1st & end on December 31st.

Are you 100% sure this is what you want to do?

Regards,

Nathan

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/