Calculate/sum up values from Weekend-days to next monday after weekend

[KvR]

Hello, I could not find any topics with this particular problem, maybe I did not seach well enough.

I have this dataset with total values on dates (like total sales per day), and the total values of the weekend-days (saturday and sunday) needs to be added up to the total values on the next monday. We want to calculate our results per day, but because in the weekends the opartions is only at 20%, it gives not a good average overall.

This is the dataset in Power BI:

Dataset presented in Power BI

This is how we calculate it very primitively in Excel (same dataset):

Values copied from pivot from same dataset

This is the matrix that is coming from the calculation in Power BI:

Values in matrix (pivot) in Power BI with lesser values on the weekend-days

It would be ideal if there is some kind of time-intelligence-like measure to handle this. The values will later be used in a KPI dashboard. So the calculation as presented on the screenshot is not the endproduct that is needed.

Anyone idea's in which direction i need to search this. I did some tries on DAX and Time intelligence to tackle this, but I am not getting any closer. Maybe it is not possible with a measure of calculated column, and do I need to think about a datawarehouse-like structur (puting in an extra table).

Thanks in advance, KvR

[KvR]

We solved it with building a function within SQL Management studio, so not in Power BI directly. Nevertheless, thanks for your help.

[v-jiascu-msft]

Hi @KvR ,

Could you please mark the proper answers as solutions?

Best Regards,

[Greg_Deckler · Accepted solution]

I would think that you could get there using WEEKDAY function and something like my Time Intelligence the Hard Way Quick Measure.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[KvR]

I will look at it later today, thanks 🙂