Power BI - add columns / filters for date 1, date 2..date n where n is number of days between two dates

power bi filter between two dates
power bi dynamic date filter
power bi date slicer default to today
power bi multiple date filters
power bi date range filter
dax filter between two dates
power bi dynamic date parameter
dax dynamic date range

To explain my issue please have a look at below extract from my schema.

ID          STATE   START_DATE_PACK END_DATE_PACK   PACK    CLOSE DATE
10539395    O       10/10/2019      20/10/2019      A1  
10564163    O       10/10/2019      20/10/2019      A1  
10564181    O       10/10/2019      20/10/2019      A1  
10564574    C       10/10/2019      20/10/2019      A1      12/10/2019
10564578    C       10/10/2019      20/10/2019      A1      12/10/2019
10564580    C       10/10/2019      20/10/2019      A1      13/10/2019
10565048    C       10/10/2019      20/10/2019      A1      14/10/2019
10565069    C       10/10/2019      20/10/2019      A1      15/10/2019

What I want it the graph that shows the progress per day (between Start & End dates).

In excel it would simple stacked chart but I am struggling to do in Power BI. Note I have hundreds of Packs hence some dynamic way of ding it is required.

16-Oct Update This is my table now

ID          STATE   START_DATE_PACK END_DATE_PACK   PACK    CLOSE DATE  Column  NEW.DAYS_ON_CHECK_COUNT Column 2
10539395    O       10/10/2019       20/10/2019     A1                  Open    DAY                     8
10564163    O       10/10/2019       20/10/2019     A1                  Open    DAY                     8
10564181    O       10/10/2019       20/10/2019     A1                  Open    DAY                     8
10564574    C       10/10/2019       20/10/2019     A1     12/10/2019   Closed  DAY 2                   8
10564578    C       10/10/2019       20/10/2019     A1     12/10/2019   Closed  DAY 2                   8
10564580    C       10/10/2019       20/10/2019     A1     13/10/2019   Closed  DAY 3                   8
10565048    C       10/10/2019       20/10/2019     A1     14/10/2019   Closed  DAY 4                   8
10565069    C       10/10/2019       20/10/2019     A1     15/10/2019   Closed  DAY 5                   8

Where:

Column = IF(ISBLANK(Sheet1[CLOSE DATE]), "Open", "Close")

NEW.DAYS_ON_CHECK_COUNT = 
  VAR _days = DATEDIFF(Sheet1[START_DATE_PACK],Sheet1[CLOSE DATE], DAY)
RETURN "DAY " & _days

Column 2 = COUNTROWS(Sheet1)

My Graph is nearly there

Still does not work as a ratio of all and closed per day. Any ideas?


I would first create a Days table, containing 2 columns:

Day Label e.g. DAY 1, DAY 2, ...

Day Datediff e.g. 0, 1, ...

It might requires 10 rows to achieve your chart example - you may want to create it with extra rows for other scenarios. The simplest way would be using Enter Data, there are probably more elegant alternatives.

In the Model (diagram) view, the Days table should remain disconnected from every other table. I would set the Sort by Column for the Days Label column to the Day Number column

Next I would create a new Statuses table, with values Open and Closed in a column Status. Again this table would stay disconnected in the Model.

The last disconnected table I would create is Dates e.g. using the CALENDARAUTO function.

Next I would create a Measure to count the relevant rows for each combination of the disconnected table rows, e.g.

Daily Count = 
VAR vStart_Date =
    MIN ( Dates[Date] )
VAR vEnd_Date =
    MAX ( Dates[Date] )
RETURN
    SUMX (
        CROSSJOIN ( Sheet1, Days, Dates, Statuses ),
        SWITCH (
            TRUE,
            [Date] < vStart_Date, BLANK (),
            [Date] > vEnd_Date, BLANK (),
            [Date] < [START_DATE_PACK], BLANK (),
            [Date] > [END_DATE_PACK], BLANK (),
            DATEDIFF ( vStart_Date, [Date], DAY ) <> [Day Datediff], BLANK (),
            [Status] = "Open" 
                && [STATE] = "C" && [Date] > [CLOSE DATE] , BLANK (),
            [Status] = "Closed" 
                && [STATE] = "O", BLANK (),
            [Status] = "Closed" 
                && [Date] <= [CLOSE DATE] , BLANK (),
            1
        )
    ) 

On the page I would create a Slicer for the Dates field from the Date table, and filter for your date range (e.g. 10/10/2019 - 15/10/2019).

Finally I would add a stacked column chart, using the Day Label field from the Days table as the chart Axis, Status as Legend and use the measure Days Count as the Value.

There's parts of your requirements I can't follow from the OP, so the above might need tweaking.

I've built a working demo which you can download from this link:

https://1drv.ms/f/s!AGLFDsG7h6JPgw4

The file name is: Power BI demo - Progress per day between Start and End Dates.pbix.

Here's how it looks:

Use a relative date slicer or filter in Power BI, How do you create a date range in power bi? With the relative date slicer or relative date filter, you can apply time-based filters to any date column in your data model. For example, you can use the relative date slicer to show only sales data that's happened within the last 30 days (or month, calendar months, and so on).


from your data you need to add a column that can be used as a legend.

If that is your schema direct from the table you can do it in Power Query or Dax

In Power Query add a custom column

if [Close Date] = "" Then "Open" else "Close"

In DAX you can add a custom column as well with the logic

Column = IF(ISBLANK('tablename'[Close Date]), "Open", Close")

You can then drag that as the legend, and the 'Start Date' as a value, with an aggregation of count, Or create a new measure with COUNT('tablename'[StartDate]) which should do the same.

Hope that helps

Update: Your example is showing the correct split, I think you need to allocate the open items to the number of days

NEW.DAYS_ON_CHECK_COUNT =
  VAR dateCheck = IF(ISBLANK('Sheet1'[CLOSE DATE]), TODAY(), 'Sheet1'[CLOSE DATE])
  VAR _days = DATEDIFF(Sheet1[START_DATE_PACK],dateCheck  , DAY)
RETURN "DAY " & _days

That should set the open ones to the number of days open from the start date to today, So those items should appear as 'Days 6', tomorrow they will be 'Days 7' if not closed.

Solved: Generating a table with values for all dates betwe , Generating a table with values for all dates between two dates I would like to pivot it to create a table with a column in a date format I know how to use Power query or DAX to calculate the number of days between Message 2 of 6 to powerbi designer slect modelling tab and create a new table using  Thank you for this post. Can you help me take this a step further? In a table with data related to projects, I'm evaluating 2 date columns - Planned Gate meeting date (my Start Date) and Actual Gate meeting date (my End Date). I've added a calculated column to my table with your formula (Planned Gate Date - Actual Gate Date)*1. However, I have


Have you tried using another visualization "100% Stacked column chart" rather? That will have 100% chart with options like yours Open & Close.

Hope this what you are after. Let me know how you go!

See the attached image

Relative Date Filters in Power BI / DAX / Power Query, Creating relative date references for reporting purposes in Power BI / Power Pivot using DAX and Power Query and a Calendar Table. days ahead of today and a negative number for the days prior to today's date and with those Method 2: Using the Relative Dates Slicer Filter in Power BI From ( Date. I am trying to do something that is very easy in Excel but haven't found the correct syntax in Power BI yet. I need to create a measure that calcualtes the number of days between two dates. I am not looking to do anything special like network days just plain old calendar days between dates in Column A to dates in Column B.


Using DATEADD() and other Date Tips for Filtering Data in Power BI , Creating a relative date slicer and filter in Power BI Power BI Desktop no date filter, you can apply time-based filters to any date column in your data model happened within the last 30 days (or month, calendar months, and so on) from May 1 through June 30 (the last two complete calendar months). Case 3: Fill specific day of the week between dates. In the post that I previously mentioned on “Recurring dates”, I basically play with the last parameter of the List.Dates function to get only dates that will have a “gap” of x amount of days between them which I define using the last parameter of this List.Dates function with the duration.


Calculate Amounts Sold Between Two Dates In Power BI w/DAX , While working with Dates in DAX, you may have come across an interesting SQL Syntax DATEADD (datepart, number, date ) distinction between these two functions is the what date parameter. Scenario 2 – Determine Current Month We can get the Month from Today's Date and subtract 1 to get the  Ejo: I walked into this now, almost a year later. I’m quite noob with this but my solution is that before you expand, you substract the end date-start date, then add custom column 1 and divide this with the previous number. You will get a number 0,00x…. on each row, then you can use this as a multiplier to multiply your budget, etc. numbers.


Filtering and comparing different time periods with Power BI, 1:13 - Rolling Totals Using The DATESBETWEEN Function Running Totals in Power BI: How Duration: 5:25 Posted: Sep 12, 2017 Good afternoon: I am trying to make a measure that reflects in a table the total cost Budgeted by day, month and year ONLY UNTIL the last date of the "Real" table. I have (among others) the following tables in my data model: - Calendar - Ppto: Table of costs budgeted from 01/01/2019 to 31/12/2019 -