calculate time progress with DAX

At my last customer appointment, the customer asked for a measure to represent the time progress of a variable period in relation to today’s date.

As base for the calculation of the measure time progress, i use the date table form my blog post Erstellen einer einfachen Datumstabelle in DAX.

First we use COUNTROWS to determine the number of days for the filtered total period.
Then we count the number of days, for the filtered period, that are before or on today’s date.
Adding 0 ensures that, for a filtered period after today’s date, not BLANK but 0 is returned.
Finally, we relate the number of days to each other.

time progress = 
VAR TotalPeriod =
    COUNTROWS ( 'Date' )
VAR PeriodUntilToday =
    0
        + COUNTROWS ( FILTER ( 'Date'; 'Date'[Date] <= TODAY () ) )
RETURN
    DIVIDE ( PeriodUntilToday; TotalPeriod )

With the measure of time progress you can show that we today on 30.10. already completed 96.77% of the month, 32.61% of the quarter and 83.01% of the year.

time progress by period

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

I accept that my given data and my IP address is sent to a server in the USA only for the purpose of spam prevention through the Akismet program.More information on Akismet and GDPR.