YTD for different date format without Date column

[snosyw2]

in the model i have dim_bridge_date table as follows: 

its linked with my fact_general_actual table

dim_bridge_date table is created because the date in the fact depends on the frequency of the kpi and i only have Quarterly, Monthly, Semi-Annual Frequencies
im trying to get YTD for the actual value how can i do it

[danextian · Accepted solution]

Hi @snosyw2 

I would use a date equivalent of the date mix key (date for daily and end of month of monthly) and run the ytd calculation using that column. Example:

YTD Value =
CALCULATE (
    [value],
    FILTER (
        ALLEXCEPT ( datesdim, datesdim[mix type] ),
        datesdim[date equivalent] <= MAX ( datesdim[date] )
            && datesdim[year] = MAX ( datesdim[year] )
    )
)

If this doesn't work, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[v-sgandrathi]

Hi @snosyw2,

 Thank you @Kedar_Pande @grazitti_sapna  and @danextian for your replies in the query and for your contribution to the community forum.

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

Thank you.

[v-sgandrathi]

Hi @snosyw2,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

Thank you.

[Kedar_Pande]

@snosyw2 

You can't calculate YTD without a proper date column. Your dim_bridge_date table needs a continuous date column, even if some periods are blank.

Add a date column to dim_bridge_date that represents the period end date

YTD Actual =
TOTALYTD(
SUM(fact_general_actual[value]),
dim_bridge_date[your_new_date_column]
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[grazitti_sapna · Accepted solution]

Hi @snosyw2 ,

Assuming your relationship:- fact_general_actual[mix_date_key] → dim_bridge_date[mix_date_key]
Make sure you have actual date field in dim_bridge_date

Create a measure for YTD:- 
Actual YTD =
VAR CurrentYear = SELECTEDVALUE(dim_bridge_date[year])
VAR CurrentDate = MAX(dim_bridge_date[Date])
RETURN
CALCULATE(
SUM(fact_general_actual[value]),
FILTER(
ALL(dim_bridge_date),
dim_bridge_date[year] = CurrentYear &&
dim_bridge_date[Date] <= CurrentDate
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[danextian · Accepted solution]

Hi @snosyw2 

I would use a date equivalent of the date mix key (date for daily and end of month of monthly) and run the ytd calculation using that column. Example:

YTD Value =
CALCULATE (
    [value],
    FILTER (
        ALLEXCEPT ( datesdim, datesdim[mix type] ),
        datesdim[date equivalent] <= MAX ( datesdim[date] )
            && datesdim[year] = MAX ( datesdim[year] )
    )
)

If this doesn't work, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.