Values shown on wrong date

[Anonymous]

Hi, 

I am fairly new to Power BI and here is something that I cannot solve on my own right now. I have a report where I want to show a dynamic axis for the timeframe that is selected by the user (year, quarter, month, week) via a slicer. To achieve this I have unpivoted my 'Calendar' table into a second table called 'Calendar_Unpivot' like this:

Timeframe	Date	Date_agg
Quarter	27-8-2019 00:00	1-7-2019 00:00
Year	27-8-2019 00:00	1-1-2019 00:00
Week	27-8-2019 00:00	26-8-2019 00:00
Month	27-8-2019 00:00	1-8-2019 00:00

I have also created a one-to-many relation between Calendar[Date] and Calendar_Unpivot[Date]. I crossfilter the values in my DAX measure formula to ensure that the value shown for the 'Costs' is correct. All is good and seems to be working alright... until I want to compare the values through something like SAMEPERIODLASTYEAR()!

In the first picture below you see my current output with 'Date_agg' on the axis. In the second picture you see my desired output, to achieve this at this point I put the static 'MonthName' on the axis, but I want my 'Date_agg' on the axis so that they are interactive with the selection slicer for (year, quarter, month or week).

My question is: how can I fix this? Should I use a total different solution then my 'Calendar_Unpivot' or is there an (easier) fix that I am overlooking right now? You're help will be much appreciated.

CurrentDesired

[amitchandak]

You have to make sure, your date, month year on axis comes from the date table.

you can use trailing year

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

This file has few such example https://www.dropbox.com/s/bu47m5ek7sr9v8t/sales_analytics_v2.pbix?dl=0

[Anonymous]

@amitchandak  First of all, thank you for your quick response! But when I do what you suggested (change the axis to a field of the Calendar table) then I will not have a dynamic axis. What can I do to make my axis dynamic based on the slicer selection by the user?

[amitchandak]

@Anonymous, I think for the measure you can use Slicer, bit for axis I think you have to use the bookmark

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive