YTD and LYTD calculation Issue

[ROCKYDO12]

Hello,

I am trying to create some KPI's and I am using the following calculations for CYTD Sales and LYTD Sales:

CYTD Sales: CALCULATE(SUM('Sales Base'[Amount]),DATESYTD(('Calendar'[Date]),"3/31"))

LYTD Sales:  CALCULATE(SUM('Sales Base'[Amount]),DATESYTD(dateadd('Calendar'[Date],-1,Year),"3/31"))

I have added a date calender and have a relationship between my sales table and calender table. I am having an issue as the amounts are showing full year amounts, instead of YTD. Then when I try to use the Sales table dates inplace for calender dates it shows up as blank. 

[danextian · Accepted solution]

Hi @ROCKYDO12 ,

Your measures should work. There are other peculiarities in your model that could be causing this. Please see this PBIX for you reference - https://drive.google.com/file/d/108pq_k-N8CPQt5hTsaZgU8rP9h4L2dKs/view?usp=sharing 

[danextian]

Hi @ROCKYDO12 ,

How does the relationship between the two table flow?

[ROCKYDO12]

[danextian]

Hi @ROCKYDO12 
I was referring to the cross filter direction. 

[ROCKYDO12]

Single, same as yours! Not sure if this could play a factor but my calender table the date has a hierarchy and my sale table the date table does not.

[danextian · Accepted solution]

Hi @ROCKYDO12 ,

Your measures should work. There are other peculiarities in your model that could be causing this. Please see this PBIX for you reference - https://drive.google.com/file/d/108pq_k-N8CPQt5hTsaZgU8rP9h4L2dKs/view?usp=sharing 

[ROCKYDO12]

Thank you for this! I am trying to create a YTD slicer that will allow me to flip between full year and YTD. Essentially I am thinking I would have one field for amount and one for Previous year amount using SAMEPERIODLASTYEAR. Then I would need a slicer so i can flip back and fourth. Could you assit with this?

[danextian]

Hi @ROCKYDO12 ,

Try the fields parameter feature. Update your version of Power BI Desktop if it isn't available.

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters 

[ROCKYDO12]

How would this work if I have 9 visuals showing full year amounts for current year and previous year? What fields do I need to use for the parameter? Is there an easier way of just adding a slicer? 

[ROCKYDO12]

It must be my calender. I need this calender because my FY runs Arpil 1 - March 31st and also I need all the fields for filters and calculations and such. Is there any way I can make calculations work using this calender?

Calendar =
VAR FirstFiscalMonth = 4 -- First month of the fiscal year
VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ...
VAR FirstSalesDate = MIN ( 'Sales Base'[Sale Date] )
VAR LastSalesDate = MAX ( 'Sale Base'[Sale Date] )
VAR FirstFiscalYear = -- Customizes the first fiscal year to use
YEAR ( FirstSalesDate )
+ 1 * ( MONTH ( FirstSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1)
VAR LastFiscalYear = -- Customizes the last fiscal year to use
YEAR ( LastSalesDate )
+ 1 * ( MONTH ( LastSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1)
RETURN
GENERATE (
VAR FirstDay =
DATE (
FirstFiscalYear - 1 * (FirstFiscalMonth > 1),
FirstFiscalMonth,
1
)

VAR LastDay =
DATE (
LastFiscalYear + 1 * (FirstFiscalMonth = 1),
FirstFiscalMonth, 1
) - 1
RETURN
CALENDAR ( FirstDay, LastDay ),

VAR CurrentDate = [Date]
VAR Yr = YEAR ( CurrentDate ) -- Year Number
VAR Mn = MONTH ( CurrentDate ) -- Month Number (1-12)
VAR Mdn = DAY ( CurrentDate ) -- Day of Month
VAR DateKey = Yr*10000+Mn*100+Mdn
VAR Wd = -- Weekday Number (0 = Sunday, 1 = Monday, ...)
WEEKDAY ( CurrentDate + 7 - FirstDayOfWeek, 1 )
VAR WorkingDay = -- Working Day (1 = working, 0 = non-working)
( WEEKDAY ( CurrentDate, 1 ) IN { 2, 3, 4, 5, 6 } )
VAR Fyr = -- Fiscal Year Number
Yr + 1 * ( FirstFiscalMonth > 1 && Mn >= FirstFiscalMonth )
VAR Fmn = -- Fiscal Month Number (1-12)
Mn - FirstFiscalMonth + 1 + 12 * (Mn < FirstFiscalMonth)
VAR Fqrn = -- Fiscal Quarter (string)
ROUNDUP ( Fmn / 3, 0 )
VAR Fmqn =
MOD ( FMn - 1, 3 ) + 1
VAR Fqr = -- Fiscal Quarter (string)
FORMAT ( Fqrn, "\Q0" )
VAR FirstDayOfYear =
DATE ( Fyr - 1 * (FirstFiscalMonth > 1), FirstFiscalMonth, 1 )
VAR FirstDayFY =
DATE (
Fyr - 1 * (FirstFiscalMonth > 1),
FirstFiscalMonth,
1
)
VAR LastDayFY =
DATE (
Fyr * (FirstFiscalMonth > 1),
FirstFiscalMonth,
1
)
VAR Fydn =
SUMX (
CALENDAR ( FirstDayOfYear, CurrentDate ),
1 * ( MONTH ( [Date] ) <> 2 || DAY ( [Date] ) <> 29 )
)
RETURN ROW (
"DateKey", INT ( DateKey ),
"First Day in FY", FORMAT ( FirstDayFY, "dd mmm yyyy" ),
"Last Day in FY", FORMAT ( LastDayFY, "dd mmm yyyy" ),
"Sequential Day Number", INT ( [Date] ),
"Year Month", FORMAT ( CurrentDate, "mmm yyyy" ),
"Year Month Number", Yr * 12 + Mn - 1,
"Fiscal Year", "FY " & Fyr,
"Fiscal Year Number", Fyr,
"Fiscal Year Quarter", "F" & Fqr & "-" & Fyr,
"Fiscal Year Quarter Number", CONVERT ( Fyr * 4 + FQrn - 1, INTEGER ),
"Fiscal Quarter", "F" & Fqr,
"Month", FORMAT ( CurrentDate, "mmm" ),
"Fiscal Month Number", Fmn,
"Fiscal Month in Quarter Number", Fmqn,
"Day of Week", FORMAT ( CurrentDate, "ddd" ),
"Day of Week Number", Wd,
"Day of Month Number", Mdn,
"Day of Fiscal Year Number", Fydn,
"Working Day", IF ( WorkingDay, "Working Day", "Non-Working Day" )
)
)

[danextian]

Can you share sanitized copy of your pbix? It is easier to figure it out that way.

[ROCKYDO12]

https://drive.google.com/file/d/1qaOlCvRtuGBq-dn3owpuY5MugbERRK9J/view?usp=sharing

[danextian]

Please make this available to the public.

[Ashish_Mathur]

Access Denied message.

[ROCKYDO12]

Test.pbix (3,087K)

[danextian]

That is a link to a mail attachment. Share using GDrive or OneDrive.

[ROCKYDO12]

https://drive.google.com/drive/folders/1NxKT7WpxcVw-VFQIRdyNNIktxBw-Pn21?usp=sharing

[danextian · Accepted solution]

Hi @ROCKYDO12 ,

Your measures work fine after creating the relationship which was not in the file you attached.

https://drive.google.com/file/d/1kcxiM9q401vRiMzYKE6QMhMeUQBPa58x/view?usp=sharing 

[Ashish_Mathur]

Hi,

I get an error when i click on that link.

[ROCKYDO12]

https://drive.google.com/drive/folders/1NxKT7WpxcVw-VFQIRdyNNIktxBw-Pn21?usp=sharing

[Ashish_Mathur]

I get this message when i try to open the file.