FYTD Daily % Measure Needed -- DAX

[bw70316]

what would the DAX equation be to achieve the daily FYTD % numbers. Basically, July 1 = 1/365, July 2= 2/365, etc. So that June 30 = 365/365

[v-juanli-msft · Accepted solution]

Hi @bw70316

If you want to calcuate values based on the date in this period: year/7/1~year+1/6/30,

Ashish_Mathur and mattbrice provide good solutions, 

as tested, i use their formula to create two measures:

s1 = CALCULATE(SUM('Data'[value]),DATESYTD('Table'[Date],"6/30"))

s2 = TOTALYTD(SUM('Data'[value]),'Table'[Date],"6/30")

to get the percent as shown, 

please create calcuated columns in the calendar table (called "Table" in my test)

year = YEAR('Table'[Date])

month = MONTH('Table'[Date])

day = DAY('Table'[Date])

total_days =
IF (
    [month] >= 7,
    DATEDIFF ( DATE ( [year], 7, 1 ), DATE ( [year] + 1, 6, 30 ), DAY )
        + 1,
    DATEDIFF ( DATE ( [year] - 1, 7, 1 ), DATE ( [year], 6, 30 ), DAY )
        + 1
)

then create measures:

count_date = CALCULATE(COUNT('Table'[Date]),DATESYTD('Table'[Date],"6/30"))

FYTD % = [count_date]/MAX('Table'[total_days])

FYTD _format = [count_date]&"/"&MAX('Table'[total_days])

Best Regards

Maggie

“
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
“

[v-juanli-msft · Accepted solution]

Hi @bw70316

Do these replies help to slove your problem?

If not, please let me know.

Best regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @bw70316

If you want to calcuate values based on the date in this period: year/7/1~year+1/6/30,

Ashish_Mathur and mattbrice provide good solutions, 

as tested, i use their formula to create two measures:

s1 = CALCULATE(SUM('Data'[value]),DATESYTD('Table'[Date],"6/30"))

s2 = TOTALYTD(SUM('Data'[value]),'Table'[Date],"6/30")

to get the percent as shown, 

please create calcuated columns in the calendar table (called "Table" in my test)

year = YEAR('Table'[Date])

month = MONTH('Table'[Date])

day = DAY('Table'[Date])

total_days =
IF (
    [month] >= 7,
    DATEDIFF ( DATE ( [year], 7, 1 ), DATE ( [year] + 1, 6, 30 ), DAY )
        + 1,
    DATEDIFF ( DATE ( [year] - 1, 7, 1 ), DATE ( [year], 6, 30 ), DAY )
        + 1
)

then create measures:

count_date = CALCULATE(COUNT('Table'[Date]),DATESYTD('Table'[Date],"6/30"))

FYTD % = [count_date]/MAX('Table'[total_days])

FYTD _format = [count_date]&"/"&MAX('Table'[total_days])

Best Regards

Maggie

“
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
“

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(SUM(Data[Value]),DATESYTD(Calendar[Date],"30/6"))

Hope this helps.

[mattbrice]

TOTALYTD ( SUM ( Table[Value] ) , DimDate[Date], "06/30" )