cancel
Showing results for
Did you mean:
Helper I

## Prior YTD Calculations with Missing CY Data?

I am trying to provide a YOY calculation using Month and YTD based on acitvity, but cannot get my PYT calculation total everything.

For Example:

CY               PY

Jan               300                 0

Feb               250               200

March          350                180

April               0                  225

May             360                 425

Jun                0                   460

Jul                275                 0

Becuse Apr/Jun are missing CY data, it's also excluding Apr/Jun from PY data, but I need it to calculate everything.

Current formula is -

Prior YTD = CALCULATE([YTD Data],SAMEPERIODLASTYEAR([DataTableName][date]))

1 ACCEPTED SOLUTION
Microsoft

@Tihannah,

PY Total = CALCULATE([CY Total Measure],SAMEPERIODLASTYEAR('DateDimension'[Date]))

YTD Total= TOTALYTD(SUM(DataTable[Total Theo]),'DateDimension'[Date])

PYTD Total= CALCULATE([YTD Total Measure],SAMEPERIODLASTYEAR('DateDimension'[Date]))

Then create visual using Date field of DateDimension and the above measures as @MFelix's screenshot.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 REPLIES 12
Super User III

Hi @Tihannah,

You need to create a date table and make a relationship with your data table that way the months were you don't have values will also be consider.

My measures are as follows (important part is the date table used on same period last year)

```CY = SUM('DataTable'[Total]) + 0 // (+ 0 Just to avoid blanks)

PY = CALCULATE([CY] ; SAMEPERIODLASTYEAR('Date'[Date]))```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

I was initally using 'date' from my data table, but change to the date table. This is now giving the same totals as CYTD?

PYTD Total = CALCULATE([YTD Total],SAMEPERIODLASTYEAR('DateDimension'[Date]))

Super User III
How do you calculate [YTD Total]?

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

CYTD Total=TOTALYTD(SUM('DataTable'[TotalAmount],DataTable[date],ALL('DateDimension'[Month]))

I couldn't get the other YTD formula you posted to work for me at all.

Super User III
All your date references should be made to the dates table and not the the Date in your data table.

Rgards
Mfelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

It doesn't work. When I change all my refernces to the Date table, everything comes back blank. I have a date table and then I have "date" built into my data table that joins them together.

Here are my current formuals of which the first 3 areall comingback correct.

CY Total  = SUM(DataTable[Total Measure])

PY Total = CALCULATE([CY Total Measure],SAMEPERIODLASTYEAR('DataTable'[Date]))  <<If I change this to the 'Date' table then I either get the same numbers as CY or no numbers at all

YTD Total= TOTALYTD(SUM(DataTable[Total Theo]),'DataTable'[Date],ALL('DateDimension'[Date])

PYTD Total= CALCULATE([YTD Total Measure],SAMEPERIODLASTYEAR(DataTable[date]))   <<This is the only column that's missing data and changing reference to the date table eliminates all data instead of just hte missing months not present in CY.

Super User III

Hi,

Share the link from where i can download your PBI file.  Also, clearly show the incorrect Table there and the result you are expecting.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III
Do you have an active relationship betweeb the to dates?

Are you using on your visuals wich date column? From the date table or from the actual data?

Regards,
MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

Yes, there's an active relationship. Date not being used in the actual data table or visuals, but only in the filter for the report.

Microsoft

@Tihannah,

PY Total = CALCULATE([CY Total Measure],SAMEPERIODLASTYEAR('DateDimension'[Date]))

YTD Total= TOTALYTD(SUM(DataTable[Total Theo]),'DateDimension'[Date])

PYTD Total= CALCULATE([YTD Total Measure],SAMEPERIODLASTYEAR('DateDimension'[Date]))

Then create visual using Date field of DateDimension and the above measures as @MFelix's screenshot.

Regards,
Lydia

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

Thank you all. I finally figured it out. I kept getting zero values using the date table in my codes because my page filter was still using the data table's date measure. Everything seems to be pulling in correctly now

Super User III
You should use the date table on your measures and on your visual and should work.

Regards
MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors