Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Ashoop2020
Regular Visitor

Creating a Calendar Table with the First date based on column value

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. 

 

Screenshot 2022-12-15 101116.png

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

 

Thanks!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_1-1671177178534.png

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

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_1-1671177178534.png

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
Impactful Individual
Impactful Individual

@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/

 

WinterMist_0-1671121955199.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.