Forum Discussion

IanCockcroft's avatar
IanCockcroft
Icon for Post Patron rankPost Patron
6 years ago

DATE range bucket

Hi guys,

pulling my hair out.

I hve been struggling with a requirment for a good week now.

 

I have 2 tables, one is a calendar    dimension used   in a filter.

the other is details of incident logs. they are related by date.

 

I need to show a range  of dates depending on the section. 7 days before and 7 days after.

so if the 20th is seected, the grid needs to show all incidents from the 13th to the 27th

I have been trying all sorts of DAX but  just  cant  get it  right.

any ideas would be most welcome

thannks

Ian

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    IanCockcroft , You can try like with date table

    Rolling 7 days = CALCULATE(sum(Sal[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-7,Day))

    Rolling 14 days = CALCULATE(sum(Sal[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-14,Day))

     

    Rolling 3 till last 2 Day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,Day)),-3,Day))

     

    Some time it will roll data in one date. So if you need different dates , have a look at

    https://www.youtube.com/watch?v=duMSovyosXE

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    To do this, you can create a separate disconnected table just for your date values in the slicer with an expression like this.

     

    DateForSlicer = VALUES('Date'[Date])
     
    Put the above in your date slicer, and then make a table visual with your original Date[Date] column and this measure
    New Measure =
    VAR thisdate =
    SELECTEDVALUE ( DateForSlicer[Date] )
    RETURN
    CALCULATE (
    [Total Sales],
    KEEPFILTERS ( 'Date'[Date] <= thisdate + 7 && 'Date'[Date] >= thisdate - 7 )
    )
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    IanCockcroft I would suggest creating a disconnected date table (another date table without any relationships to other tables), called "Selected Date".  Modeling --> New Table

     

    Selected Date = CALENDAR(date(2020,7,1),today())

     

    You use the "Date" column from "Selected Date" as your slicer.

     

    Then you can create a table/visual with the regular date table "Date" and here is the measure to show the correct values for 7 days back:

     

    7 Days Back = CALCULATE([Your Measure],filter('Date','Date'[Date]<=selectedvalue('Selected Date'[Date])&&'Date'[Date]>=selectedvalue('Selected Date'[Date])-7))
     

     

    For useability with multiple measures, you could include a measure picker or utilize calculation groups instead!

     

    For calculation groups:

    1. Make sure you have Tabular Editor installed

    2. Go to Exernal Tools ribbon and choose Tabular Editor

    3. Model --> New Calculation Group. I named mine "Selected Date Calcs"

    4. Calculation Group Table --> Create New -->Calculation Item. I named it "7 days back"

    5. Paste in the measure above, and change out the [Your Measure] with SELECTEDMEASURE()

     

    CALCULATE ( SELECTEDMEASURE (), FILTER ( 'Date', 'Date'[Date] <= SELECTEDVALUE ( 'Selected Date'[Date] ) && 'Date'[Date] >= SELECTEDVALUE ( 'Selected Date'[Date] ) - 7 ) )

    6. Save the changes 

    7. Go back to the Power BI Desktop, it should ask you to refresh the calculation group

    8. Click refresh now

    9. Find your calculation group, and add the column called "Name" into a slicer.

    10. Now you can use any measure in your model in the table, and simply click the "7 Days Back" and a date from your "Selected Date" slicer to see it 7 days back!

     

    Hope this helps!

     

    You can also create a 7 Day Forward one, and use them together or separately.