Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Values shown on wrong date

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:

 

TimeframeDateDate_agg
Quarter27-8-2019 00:001-7-2019 00:00
Year27-8-2019 00:001-1-2019 00:00
Week27-8-2019 00:0026-8-2019 00:00
Month27-8-2019 00:001-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


Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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