Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX for date range returning data from date slicer

  • 1.  DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 20, 2019 08:21 AM
    Hi,

    I'm trying to create a DAX expression that will enable me to return data when a task is in progress between a selected date range.
    eg: a Task Start date is:01/01/19 and the Task End Date is: 10/01/19 (2 seperate fields in my table)

    When i select a date range of 02/01/19 to 08/01/19 using a date slicer, I need this data to still appear because the task is still in progress.
    I have managed to create a filter which shows the data if either of the start and end dates are in the date range but as soon as I select a range that starts above the start date and ends below the end date my logic doesn't work.
    Help - how can I create the right DAX expression to show this data when the range of dates is between the date slicer start/end date.




    ------------------------------
    Rebecca


    ------------------------------


  • 2.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Mar 20, 2019 11:43 AM
    Hi Rebeca,

    I would recommend you to read Chris Web blog pst regarding event in progress, below is the link;

    https://blog.crossjoin.co.uk/2011/03/15/events-in-progress-part-3-average-age-of-unpaid-invoices/

    https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Sr. BI Consultant
    Karachi, Pakistan
    ------------------------------



  • 3.  RE: DAX for date range returning data from date slicer

    Posted Mar 24, 2019 02:55 AM

    Hi Rebecca

    I'm interested in your problem & have tried to solve by reading the links provided by Hasham, but haven't got it to work.
    If you have solved can you please post solution / more info.
    thanks

    Peta
    (keen DAX enthusiast)



    ------------------------------
    Peta
    Perth
    Australia
    ------------------------------



  • 4.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 25, 2019 05:55 AM
    Hi Peta,
    I also did try to use the links provided by Hasham but I still couldn't figure it out.  I then decided to just work logically and create some IF statements which do seem to work.  My solution below:

    DateFlag =
    var startDaterange = MIN('daterange'[date])
    var endDaterange = MAX('daterange'[date])
    return
    SUMX(
    'ChangeDetailsGeneral20 03 19'
    ,
    var startdate = 'tablename'[chg_Scheduled_Change_Start_Date]
    var enddate = 'tablename'[chg_Scheduled_Change_End_Date]
    return
    IF(AND(enddate>startDaterange,enddate<endDaterange),1,
    IF (AND(startdate>startDaterange,startdate<endDaterange),1,
    IF(AND(startDaterange>startdate,endDaterange<endDate),1
    ,0)
    )))


    Startdate and enddate are the fields in my data
    'daterange'[date] is a date table which isn't linked to my data but is used as the date slicer

    I am not sure if this is the best way to do it, but it does work for all my scenarios except one which I'm currently still trying to figure out.  When the start date and end date of my slicer indicates this task is in progress during the date range slicer then the date flag is higher than 0.
    However if I put the start and end date in the slicer to be the same as the start date of the task then it doesn't work, but if i put the start and end date of the slicer as the same as the end date of my task it does work.  (This is really confusing)







    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 5.  RE: DAX for date range returning data from date slicer

    Gold Contributor
    Posted Mar 24, 2019 02:00 PM
    Hey Rebecca!

    May be this can solve your issue

    =CALCULATE(SUM(Table1[Columns1]), DATESBETWEEN(DateTime[Column2]
        DATE(2019,01,02),
        DATE(2019,01,08)
      ))

    SAM

    ------------------------------
    Sam Chatterjee
    Business Intelligence Developer
    Sam Analytiks
    (M):-(+45) 9163 4228
    URL:- https://www.samanalytiks.com
    ------------------------------



  • 6.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 25, 2019 05:58 AM
    Hi Sam,
    I'm not sure how this compares the start and end date of my data with the start and end date of the date slicer?
    Am I reading your reply incorrectly?


    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 7.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Mar 25, 2019 01:21 AM
    Hi Rebecca,

    Is this what you are looking for ?
    This is a bit of work around, you don't need to join Date table with your table but filter out thru MIN/MAX date of slicer.

    Regards

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    karachi
    3452523688
    ------------------------------

    Attachment(s)

    pbix
    Inprogress.pbix   75K 1 version


  • 8.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 25, 2019 05:34 AM
    Hi,
    I am not sure your logic entirely works.  I downloaded the file and changed the date range to 05/01/19 - 09/01/19 but TaskName B is still shown on the report however the start date for Task B is after the end date of the filter so this task wouldn't still be in progress.

    I have (I think) come up with a solution which I will post shortly... but I am not 100% sure it's the best way of filtering out this data but it does work.

    Thank you for your help.
    It is appreciated.



    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 9.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Mar 25, 2019 06:00 AM
      |   view attached
    Little modification in the formula, hopefully this will work for you.

    Looking forward for your solution.

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    karachi
    3452523688
    ------------------------------

    Attachment(s)

    pbix
    Inprogress.pbix   81K 1 version


  • 10.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 25, 2019 07:42 AM
      |   view attached
    Hi Farhan,
    I've made a further modification and I think this now works... it was missing the flag if the start date was higher than the start date but lower than the end date, and the end date was higher than the start date but lower than the end date.  Hope this makes some sense.  I've tested this on several scenarios and it seems to work.  Please could you take a quick look?  I've attached a modified PBI file.  It has multiple Measures just so I could see where the logic was failing, but i've added the full DAX formula into the Progress flag.

    Progress =
    Var EndDate = MAX(Task[EndDate])
    Var StartDate = MAX(Task[StartDate])
    Var EndProgress = IF (and(EndDate>=[StartDate], EndDate<=[EndDate]),1,0)
    Var StartProgress = IF(and(StartDate>=[StartDate],StartDate<=[EndDate]),1,0)
    Var BetweenProgress = IF (AND([startdate]>= StartDate, [EndDate]<=EndDate),1,0)
    RETURN
    IF(StartProgress = 1 || EndProgress = 1 || BetweenProgress = 1,1,0)

    Thanks

    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------

    Attachment(s)

    pbix
    InprogressRJ.pbix   78K 1 version


  • 11.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Mar 25, 2019 08:06 AM
    Yes this looks fine, but can you please clarify that :

    - If task is completed between slicer selected dates ? Lets say if slicer start date is "8-Jan-2019" and end date is "16-Feb-2019" and task start date is "15-Jan-2019" and Task End date is "23-JAN-2019"...... is it completed or inprogess ?


    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    karachi
    3452523688
    ------------------------------



  • 12.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 25, 2019 08:19 AM
    Hi,
    In that scenario the task would be flagged in-progress during those dates so I would expect to see it on any report using the flag I just created (with your help).
    Sorry I probably didn't explain myself properly about what I'm trying to achieve.  This flag is so I can report on the scheduled work for teams.  So if they have a task that is scheduled to run at any time between the dates entered on the report (whether it's been started or not by the team) I need it to show on the report so we can guage what workload they have coming up.  This flag is so they can view upcoming work for the week but using normal date slicers doesn't work if you start a task the previous week and it's ongoing into the week you are pulling the reporting for.

    Hope this makes sense?

    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 13.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Mar 25, 2019 08:27 AM
    Yes, perfect!!!!

    Thanks for the explanation....

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    karachi
    3452523688
    ------------------------------



  • 14.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 25, 2019 10:23 AM
    Hi Farhan,
    Just a slight tweak to the code, as it wasn't picking up my first date (i think it got confused as it also has a time stamp against the date)
    So I added a datevalue to ensure it's just looking at the date.

    Progress =
    Var EndDate = datevalue(MAX(Task[EndDate]))
    Var StartDate = datevalue(MAX(Task[StartDate]))
    Var EndProgress = IF (and(EndDate>=[StartDate], EndDate<=[EndDate]),1,0)
    Var StartProgress = IF(and(StartDate>=[StartDate],StartDate<=[EndDate]),1,0)
    Var BetweenProgress = IF (AND([startdate]>= StartDate, [EndDate]<=EndDate),1,0)
    RETURN
    IF(StartProgress = 1 || EndProgress = 1 || BetweenProgress = 1,1,0)

    Now it works perfectly in my own report.  It already worked correctly in the one you sent over as that only has dates in the data.

    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 15.  RE: DAX for date range returning data from date slicer

    Posted Mar 25, 2019 06:24 AM
    Hey Rebecca

    Yep confusing.
    I've just tried Hasham's latest fix & still showing closed items after slicer end date.
    Sorry I'm no help.
    Good luck
    Peta

    ------------------------------
    Peta
    Perth
    Australia
    ------------------------------



  • 16.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Mar 25, 2019 07:43 AM
      |   view attached
    Hi Peta,
    I think i solved it.. see attached.  I've also replied in the previous message with the DAX code.


    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------

    Attachment(s)

    pbix
    InprogressRJ.pbix   78K 1 version


  • 17.  RE: DAX for date range returning data from date slicer

    Posted Mar 25, 2019 08:59 AM

    Good work Rebecca

    Do you have a count of each column e.g. Progress Total = 8, Start Progess Total = 4 etc
    I can't work out how to do it as my measure:

    Total Progress =

    COUNTAX(Task, [Progress] = 1)
    doesn't take account of the date slicer.
    Can anyone help with a formula for this.
    thanks



    ------------------------------
    Peta
    Perth
    Australia
    ------------------------------



  • 18.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Aug 09, 2019 11:14 AM
    Hi Peta,
    Sorry for the late reply, yeah I'm stuck with that too so if anyone else has any great ideas on how to get round this I would love to know.

    If I manage to figure it out I will let you know.

    Thanks
    Rebecca

    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 19.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Aug 12, 2019 08:51 AM
      |   view attached
    Hi @Rebecca Jones

    Pl see if the below helps you.

    I have changed the DAX for the status measure.
    Status = 
    -------------------------------------------------------------------
    //initial variables
    VAR _mindate =
        MIN ( 'Date'[Date] )
    VAR _maxdate =
        MAX ( 'Date'[Date] )
    VAR _startdate =
        SELECTEDVALUE ( Task[StartDate] )
    VAR _endate =
        SELECTEDVALUE ( Task[EndDate] ) 
    --------------------------------------------------------------------
    //find the status of each project
    VAR _statusflag =
        SWITCH (
            TRUE (),
            _startdate > _maxdate, BLANK (),--exclude projects with start date after the slicer max date
            _startdate <= _maxdate
                && _endate > _maxdate, "In-Progress",--include projects started before the slicer max date but with end date after the slicer max date
            _startdate <= _maxdate
                && _endate = BLANK (), "In-Progress",--include projects started before the slicer max date but with end date not yet filled in (assumption that these are ongoing projects as on date and hence end date is not filled)
            _endate >= _mindate
                && _endate <= _maxdate, "In-Progress", --include projects completed between the slicer selected dates
            BLANK ()
        ) 
    ----------------------------------------------------------------------
    /* calculate the Total count of projects whose status is True.
    This value will be used to get the total count in the visual */
    VAR __relvntable =
        FILTER (
            Task,
            Task[StartDate] <= _maxdate
                && Task[EndDate] > _maxdate
                || Task[StartDate] <= _maxdate
                    && Task[EndDate] = BLANK ()
                || Task[EndDate] >= _mindate
                && Task[EndDate] <= _maxdate
        )
    VAR _totalcount =
        COUNTROWS ( __relvntable ) 
    ----------------------------------------------------------------------
    RETURN
        --for total row put the count of relevant projects
        IF (
            ISINSCOPE ( Task[TaskName] ),
            _statusflag,
            _totalcount
        )
    ​

    PFA the pbix file for your reference. ​

    ------------------------------
    Gopa Kumar S
    Limner Consulting
    ------------------------------

    Attachment(s)



  • 20.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Aug 13, 2019 04:45 AM
    Hi,
    Thanks so much!  I will give this a go (it might take me a few days to get my head round it and convert the fieldnames to our live data)
    I will let you know how i get on :)


    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 21.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Aug 14, 2019 06:12 AM
      |   view attached
    Hi Gopa,
    I'm having a few issues with this.
    Firstly after changing the fields from the sample dataset I created to our actual data, SELECTEDVALUE and ISINSCOPE say they aren't a function and the fields that I want to replace in the DAX aren't being recognised?
    (Sorry I'm relatively newbie to DAX)

    Attached a screen print if this is useful.
    Could you spot what i'm doing wrong?  (I have updated my PowerBI software version in case it's a new function)
    Thanks
    Rebecca

    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 22.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Aug 14, 2019 07:39 AM
    Hi @Rebecca Jones

    ​Is your table in a Direct Query Mode? If so, maybe these are functions not optimized for Direct Query. Please see the following link for further details on this....

    DAX formula compatibility in DirectQuery mode


    So please make the following changes to your DAX and see if it works.

    1. Instead of :
    SELECTEDVALUE(Task[StartDate]) ​

    use

    IF(HASONEVALUE(Task[StartDate],VALUES(Task[StartDate]))


    2. Instead of:

    IF (
            ISINSCOPE ( Task[TaskName] ),
            _statusflag,
            _totalcount
        )
    ​
    use
    IF ( HASONEVALUE ( Task[TaskName] ), _statusflag, _totalcount)
    Please see if the above works.

    ------------------------------
    Gopa Kumar S
    Limner Consulting
    ------------------------------



  • 23.  RE: DAX for date range returning data from date slicer

    Bronze Contributor
    Posted Aug 15, 2019 07:37 AM
      |   view attached
    Hi Gopa,
    Sorry I'm an end user (with some reporting experience) so this is quite technical for me and I'm not sure if the table is in direct query mode as this is all handled by our BI team.
    I have tried your suggested changes but it's still having some issues with the code and now giving me an error "unexpected parameter". I've attached a screen print to assist.  Your help is very much appreciated!

    Thanks
    Rebecca


    ------------------------------
    Rebecca Jones
    IT Support and Market Coordinator
    ------------------------------



  • 24.  RE: DAX for date range returning data from date slicer

    Top Contributor
    Posted Aug 15, 2019 07:53 AM
    Edited by Gopa Kumar Sivadasan Aug 15, 2019 07:58 AM
    Hi @Rebecca Jones

    Pl check your closing brackets

    I apologize. In my earlier message the ")" after the HASONEVALUE was missed out. Sorry about this. Pl see if this solves your issue.
    ------------------------------
    Gopa Kumar
    Limner Consulting

    ------------------------------