Show a value in matrix table for dates in an interval (Direct Query fields)

[Anonymous]

Hi!

I want to show a value in between start and end dates in a matrix table.

I have a simple Claendar with a date, related to start date.

This is my calculated column measure I am struggling with:

ShowDaysinPeriod =

Var startdate = 'Direct Query - workorder'[Planned Start Date]

Var enddate ='Direct Query - workorder'[Planned End Date]

return

If(min('Calendar'[Date])=startdate;1;0)

//&&max('Calendar'[Date])<=enddate;1;0)

But I would like there to be for example a "1" in each date from start date to end date for each row.

Grateful for help.

[Anonymous · Accepted solution]

I'm not sure if this is the best way but it solved my problem thanks to v-ljerr-msft   https://community.powerbi.com/t5/Desktop/Calculate-value-between-two-dates/m-p/167497#M73177

Calculate(

    count(DimDate[Date]);

    Filter(

        DimDate;

        'DimDate'[Date]>=MIN('Direct Query - workorder'[Planned Start Date])

        &&'DimDate'[Date]<= max('Direct Query - workorder'[Planned End Date])

    )

    )

[Anonymous]

No, unfortunately not:

[Greg_Deckler]

@Anonymous Can you post sample data as text? 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

I enclose sample data.
https://www.swisstransfer.com/d/e9a90d45-6d89-428d-b239-8d83731ee8cf 
I want to have a value in each cell within the duration Planned Start Date-Planned End Date.
So if the duration is 5 days ie starts on the 10th of March and finishes on the 14th of March, then I want a "1" in each date cell from the 10th-15th of March, so total of 5 "1"s for that row.
My aim is to in a matrix table visualise the duration of the "No." by formatting the background where the value is 1. Then I can see which No.s are running at a given date (week below).

[Greg_Deckler]

@Anonymous Maybe:

ShowDaysinPeriod =
Var startdate = 'Direct Query - workorder'[Planned Start Date]
Var enddate ='Direct Query - workorder'[Planned End Date]
Var mindate = min('Calendar'[Date])
Var result = If(mindate>=startdate && mindate<=enddate ;1;0)
return
result