Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tihannah
Resolver I
Resolver 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]))

 

Can someone please help??

1 ACCEPTED SOLUTION

@Tihannah,

Please change your measures to the following .

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.

View solution in original post

12 REPLIES 12
MFelix
Super User
Super User

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]))

 

sameperiod.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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]))

How do you calculate [YTD Total]?

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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
https://www.linkedin.com/in/excelenthusiasts/

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

@Tihannah,

Please change your measures to the following .

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.

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

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

Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.