Forum Discussion
Calculate total between two dates while ignoring date date selection
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] )
)
)
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.
11 Replies
Hi Anonymous ,
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?
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.
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.
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.
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.
