Calculate total between two dates while ignoring date date selection

[FarihaImami]

Hi. 

I hope I can explain my issue well. Maybe I am overthining this.

I have a report where user can select Year, Month, Week individually. I have a table with total production/runtime and other information. I want to add Average daily production YTD on this table based on ONLY selected year or years. Basic formula for this: 

Average daily production YTD = total production for selected / number of days selected

Number Of Days Selected= DATEDIFF(FIRSTDATE('datetable'[StartOfYear]), LASTDATE('datetable'[endofyear]),DAY)+1

The solution I have now works perfectly if I only have year/years selected. However, as soon as month is selected it goes haywire since production number is reduced based on month and week. How do I calculate total production for selected years between Jan 1st of lowest year selected and last day of highest year selected. I have tried solution presented as following in another thread, but it does not work when month/week is selected. TIA. 

Measure = 
CALCULATE (
    SUM ( ValueTable[Value] ),
    FILTER (
        ValueTable,
        ValueTable[Date] >= MIN( PeriodTable[StartDate] )
            && ValueTable[Date] <= MAX ( PeriodTable[EndDate] )
    )
)

[FarihaImami · Accepted solution]

Got some help from internal Power BI. Created two columns in the date table: 

StartOfYear = DATE(YEAR([Datetable]),01,01)

EndOfYear = IF(YEAR([Date])<YEAR(Today()), DATE(YEAR([Date]),12,31), TODAY()-1)

I then created a new measure as following:

Total Production for the year =

var beg = MIN('Datetable'[StartOfYear])

var eoy = MIN('Datetable'[EndOfYear])

return CALCULATE('Production'[Total Production],FILTER(ALL('Datetable'),'Datetable'[StartOfYear] = beg && 'Datetable'[EndOfYear] = eoy && 'Datetable'[Plant] in VALUES(Planttable[Plant])))

The last filter is to capture plant slicer on the page. Once this measure was created, I divided this by 'number of daye selected' measure from my first message on this thread. 

[Ashish_Mathur]

Hi,

Assuming the Year, Month and Week slicers have been created from the DateTable table, try this measure

=calculate(sum(valuetable[value]),datesbetween('datetable'[date],minx(allexcept('datetable','datetable'[year]),'datetable'[date]),maxx(allexcept('datetable','datetable'[year]),'datetable'[date])))

If this measure does not help, then share the download link of your PBI file.

[FarihaImami]

Hi Asish. Thank you for your reply. However, it did not help. I cannot share the original file due to confidential information, but I have created one as close to actual as possible. You can see the file below. Thank you for your help. 

Power BI test file 

[Ashish_Mathur]

You are welcome.  Why is there no Date column on the Production Table?  Please share the revised dataset with a Date column in the Production table.

[FarihaImami]

@Ashish_Mathur I have added the date in production table using LOOKUPVALUE. However, note that the actual file is linked up by "Measure ID" as the example file and Date column does not exist in Production Table. 

Power BI test updated with date 

[Ashish_Mathur]

That is not how it should be.  The production table should have an independent Date column.

[FarihaImami]

Sadly it does not. These data are coming from SQL database and Measure ID are what connecting date table to Production table as well as downtime record table. Does this mean I cannot do what I am looking to do? Ultimately I want a YTD daily average production number that is only based on YEAR and does not change with Month/Week/Day selection. 

As last resort, I tried setting it up as a separate visual but on the same page and it was still affected by month selection. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  I have turned off Interactions between the Month slicer and the table visual.

Hope this helps.

[FarihaImami]

Thank you Asish. However, this does not help me since on the same table I have other KPIs that does need to interact with Month slicer. So I cannot turn off the interaction. 

Looks like this challenge is more difficult than it appears. 

[davehus]

Hi @FarihaImami , 

Are you trying to retain the average for year, regardless if month is selected, so basically you do not want a month/week filter applied to this calculation?

[FarihaImami]

Yes. No month/week filter application to this measure.