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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chrisjr
Helper IV
Helper IV

set the date with the latest record as default date in a slicer

Hello All, 

 

I have a calendar table link to a sales fact table. 

 

On the front-end, I want to set the month with the latest record to be the the default date in my date slicer. 

 

The field in the date slicer are the fields "Month" "Year" that come from the calendar date. 

 

The calendar table looks like this: 

Chrisjr_0-1675338057823.png

 

Any idea how to solve this. 

 

I have found this video https://www.youtube.com/watch?v=5dN4SqufENE but I don't know where to implement the formula Today = List.Max( SalesData[Order Date])

 

Thanks,

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

Hi @Chrisjr ,

 

List.Max() is the M function which you can use in the PowerQuery editor.

You may also consider creating these two calculated columns.

Month with Current = 
IF (
    YEAR ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Year]
        && MONTH ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Month Number],
    "Current",
    'Calendar'[Month]
)

Sort one column by another column in Power BI

Sort By for Month with Current = 
IF (
    IF (
        YEAR ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Year]
            && MONTH ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Month Number],
        "Current",
        'Calendar'[Month]
    ) = "Current",
    0,
    MONTH ( 'Calendar'[Date] )
)

vcgaomsft_0-1675402307388.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

3 REPLIES 3
Chrisjr
Helper IV
Helper IV

thanks a lot  @v-cgao-msft and @amitchandak .

Thanks @amitchandak for the video, very helpful 

v-cgao-msft
Community Support
Community Support

Hi @Chrisjr ,

 

List.Max() is the M function which you can use in the PowerQuery editor.

You may also consider creating these two calculated columns.

Month with Current = 
IF (
    YEAR ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Year]
        && MONTH ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Month Number],
    "Current",
    'Calendar'[Month]
)

Sort one column by another column in Power BI

Sort By for Month with Current = 
IF (
    IF (
        YEAR ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Year]
            && MONTH ( MAX ( 'Table'[Date] ) ) = 'Calendar'[Month Number],
        "Current",
        'Calendar'[Month]
    ) = "Current",
    0,
    MONTH ( 'Calendar'[Date] )
)

vcgaomsft_0-1675402307388.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

amitchandak
Super User
Super User

@Chrisjr , Create a new column like this in DAX and use this it slicer and save on max date( column in date table)

 

Date Type = SWITCH(TRUE(),'Date'[Date]=max(fact[date]),"Max Date"
,'Date'[Date]&"")

 

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.