Forum Discussion
Issue with TotalYTD and Sameperiodelastyear
That solved the problem. I now get the values I want.
Thank you.
The set-up is now as follows:
Totalsalg YTD = TOTALYTD([Totalsalg];DATESYTD(Dates[Date]))
Totalsalg LYTD = CALCULATE([Totalsalg YTD];SAMEPERIODLASTYEAR('Dates'[Date]))
And I needed something to filter the year. Both a slicer that selects year or a matrix with year as rows works and gives me the YTD values. When adding a slicer that select Month of Year to-from I also get the eyar to date values from previous years.
Solution Supplieryou have to convert both to date (Dates[date] and fact_sales_statistics[InvoiceDate])
I created the year columns etc as described in https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/
I also changed the format of both the original date columns, Dates[date] and fact_sales_statistics[InvoiceDate] to datatype: Date
No change. Still blank values in the table.
The strange thing is that it works on a macro level using InvoiceDate as the dateparamter (even when InvoiceDate was formated as date/time). But drilling down caused gaps in the data giving me the "contiguous" error.
- sokg
what is the min and max dates in your fact_sales_statistics[InvoiceDate] ?
Min is 2nd January 2015, max i 2nd August 2018.
- sokg
Min and max in your Dates[date]???
Dates is created using the following and should be sufficient and ensuring referential integrity in the relation. Dates = CALENDAR(DATE(2012;1;1) ; DATE(2025;12;31))
The word thing is that it worked on a macro level before i introduced the date table. The date table was introduced to get around the error I got then drilling down.
- sokg
Please and a slicer with DATES[Year] and select 2018
That solved the problem. I now get the values I want.
Thank you.
The set-up is now as follows:
Totalsalg YTD = TOTALYTD([Totalsalg];DATESYTD(Dates[Date]))
Totalsalg LYTD = CALCULATE([Totalsalg YTD];SAMEPERIODLASTYEAR('Dates'[Date]))
And I needed something to filter the year. Both a slicer that selects year or a matrix with year as rows works and gives me the YTD values. When adding a slicer that select Month of Year to-from I also get the eyar to date values from previous years.