Calculation of estimates per quarters

[KatkaS]

Hello,

I have raw data as attached - number of reports (Report Legacy Key), date (Sent for Payment date) and division (Employee Org Unit 4 - Code) I would need to calculate following:

-  number of reports per month and quarter (and per divison), when I only have data for month 1 and 2 (number of reports for third month should be average of month 1 and 2 multiply 3)

Thank you very much!!

I have data for months 1 and 2 and I need to calculate estimate for third month, which is an average of the two first month.

[v-shex-msft]

Hi @KatkaS,

Did these missing date ranges exist generally in your data table? If this is a case, I'd like to suggest you create a calculated table to manually add these missed records and fill with corresponding calculation results.
After these steps, you can compare and calculate these new table records.
Regards,

Xiaoxin Sheng

[amitchandak]

Create a date table, Join with your dates. In date table have month-year too. Count the month-year. And adjust the calculation

You can use these kinds of measures.

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin