Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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??
Solved! Go to 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
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êsI 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]))
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCYTD 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIt 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
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, 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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |