cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## YTD weekly calculation

Hi everybody,

I have a measure to calculate the YTD (year to date, since 1st January) service availability of different business process. This measure is as follows:

• YTD Weekly Availability = ( (Total Elapsed Operating Time – Total Down Time) / Total Elapsed Operating Time)

Total Elapsed Operating Time = (DATEDIFF("01/01/2024",NOW(),SECOND)

Currently that measure is used in a table to show the current service availability for the different business processes.  Please see Picture attached

Now I want to show the trend of YTD by week, considering the time reference on the last day of the week (each Sunday).

I have managed to calculate the time elapsed operating time ( in an running total way) from January 1st to each Sunday of the elapsed weeks. I know the down time of each process for each week; however I am no able to properly calculate the running total of elapsed down time for each process (from 1st January to each Sunday of the elapsed weeks.

Summing up,  I don´t find the proper DAX calculation for the running total of elapsed down time. I always get the total down time for the week, not the accumulative running total

Thanks for your help or suggestions

1 ACCEPTED SOLUTION
Frequent Visitor

My mistake. Relationship between  Date Table and my date column was not OK. Now seems to be working .

Thanks a lot @amitchandak

3 REPLIES 3
Super User

@Victor_Z , Based on what I got, Have date table, joined it with Date part of your datetime column.

If needed create a table in you column

Date = Datevalue([datetime Column]) // join with date of date table

Have week created column in date table

Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
WeekDay = weekday([Date],2)

Use them in visual

And use measure

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Frequent Visitor

Thanks a lot , but it´s not working.

The measure is given me the Running Total up to now, but when adding to a visual a putting the weeks in the colum visual table , it keps giving the down time by week instead of the running total until each week.... so I have the same wrong calculation, YTD for each week not the running total YTD.

Please see the difference between Power BI calculations and Excel calculations

BR

Frequent Visitor

My mistake. Relationship between  Date Table and my date column was not OK. Now seems to be working .

Thanks a lot @amitchandak

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors