cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ExcelPBI
Post Patron
Post Patron

Dax for displaying only dates from selected range on date slicer

Hi Experts,

 

I need help in showing dates only from selected range on the date slicer. 

For example in the following snapshot of my fortnightly report, I expect the report to display only the records highlighted in yellow( as the Period selected is between 8/4/2019 to 5/5/2019)

 

fort_capture.PNG

 

Thanks in advance,

Meena

1 ACCEPTED SOLUTION

Just check if can create two more variables using MINX, MAXX - https://docs.microsoft.com/en-us/dax/minx-function-dax,

Use the filter example and find the min week start date when min week start date > Start_date of the slicer. Same way Max

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

You can try the use of slicer from here

https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks @EVEAdmin  and @amitchandak for your response.

Though the links are very useful, but i can't figure out how can i implement the solution in my scenario.

So, basically I have a table called 'Emp', that I get employees fortnightly payments from. I created a non related table called 'Dim week ending' with DAX- ALLSELECTED(Emp[Period_end_date]. Then I created a measure to calculate fortnightly payments as following:

Total fortnightly payment =

VAR _lastdate =

    CALCULATE (

        MAX ( 'Dim week ending'[Period End Date] ),

        FILTER (

            ALLSELECTED ( 'Dim week ending'[Period End Date] ),

           'Dim week ending'[Period End Date] < MAX ( 'Dim week ending'[Period End Date])

        )

    )

RETURN

    IF (

        MAX ( 'Dim week ending'[Period End Date] ) <= MAX ( ‘Emp’[Period End Date]),

        CALCULATE (

            SUM ( ‘Emp’ [Amount] ),

            FILTER (

                ALL (‘Emp’ [Period End Date] ),

                ‘Emp’ [Period End Date] <= MAX ( 'Dim week ending'[Period End Date])

                    && ‘Emp’ [Period End Date]> _lastdate

            )

        )

    )

 

I would highly appreciate if you can tell me where to include the logic for displaying only the fortnightly periods selected from the date range slicer.

 

Thanks,

Meena

The table shown in the first example may work like this

 

Measure1 =
var MIN_SLICER_DATE = MIn(Silcer(DATE))
var MAX_SLICER_DATE = MAX(Silcer(DATE))
CALCULATE (

SUM ( ‘Emp’ [Amount] ),

‘Emp’ [Period End Date] >= MIN_SLICER_DATE

&& ‘Emp’ [Period End Date] <=MAX_SLICER_DATE

)

But if the weekend date need to be calculated then more logic is needed 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Just check if can create two more variables using MINX, MAXX - https://docs.microsoft.com/en-us/dax/minx-function-dax,

Use the filter example and find the min week start date when min week start date > Start_date of the slicer. Same way Max

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

thanks @amitchandak , I don't want to disrupt my current logic of fortnight end date. Any way I can include the min & max date seprately? or perhaps merge it with existing logic?

EVEAdmin
Helper V
Helper V

I guess this tutorial will be helpful

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.