Forum Discussion
Disconnected Dates Table and YTD
Hello dear community,
I am trying to calculate YTD using a disconnected Dates Table. For YoY current month the DAX shown below worked well, but I couldn't do something similar to YTD. I am using a disconnected table, because on another graph on the same page i am showing the previously selected 13 Months in a bar chart. For example if june 2020 is picked a complete bar chart with may 19 - june 2020 will be shown - works well, the issue is only with the YTD graph.
Sales Current Month =
VAR CurrentDate = MAX('Disconnected Dates'[Testing Dates])
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, MONTH(Sales_Table[Date]) = MONTH(CurrentDate) && YEAR(Sales_Table[Date]) = YEAR(CurrentDate)))
RETURN Result
This is the DAX i used for the graph on the right side. Naturally i adjusted it to show previous period and same period 2 years ago. I would like to do something similar but for YTD.
I tried calculate and totalytd, i also have another rolling dates table for the actual slicers. Any help would be greatly appreciated, thank you !
Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
If you are using date/month on axis.
Sales YTD =
VAR _max = MAX( 'Disconnected Dates'[Testing Dates])
VAR _min = date(year(_max),1,1)
return
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, Sales_Table[Date]>= _min && Sales_Table[Date] <=Max))
RETURN ResultSales LYTD =
VAR _max = MAX( 'Disconnected Dates'[Testing Dates])
VAR _min = date(year(_max)-1 ,1,1)
return
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, Sales_Table[Date]>= _min && Sales_Table[Date] <=Max))
RETURN ResultAs such you should have both independent and joined date table
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
5 Replies
- amitchandak
Super User
Anonymous , Try measures like
Sales YTD =
VAR _max = MAXX(allselected('Disconnected Dates'), 'Disconnected Dates'[Testing Dates])
VAR _min = date(year(_max),1,1)
return
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, Sales_Table[Date]>= _min && Sales_Table[Date] <=Max))
RETURN ResultSales LYTD =
VAR _max = MAXX(allselected('Disconnected Dates'), 'Disconnected Dates'[Testing Dates])
VAR _min = date(year(_max)-1 ,1,1)
return
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, Sales_Table[Date]>= _min && Sales_Table[Date] <=Max))
RETURN Result
Sales MTD =
VAR _max = MAXX(allselected('Disconnected Dates'), 'Disconnected Dates'[Testing Dates])
VAR _min = eomonth(_max,-1)+1
return
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, Sales_Table[Date]>= _min && Sales_Table[Date] <=Max))
RETURN ResultHello amitchandak ,
thank you for your answer! I tried the above mentioned measures and had partial success - the disconnected table slicer was reacting - which is good - but i still couldn't get the actual YTD numbers. Those should be the commulative sales figures up to the selected date. Without using a disconnected dates table i used the following DAX to get the desired results:
Sales YTD = CALCULATE([Total Sales],DATESYTD(Actual_Date[Rolling Date]))## now for the year beforeSPLY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Actual_Date[Rolling Date]))SPLY YTD = CALCULATE([SPLY Sales],DATESYTD(Actual_Date[Rolling Date]))The graph using the above mentioned measured would yield something like this (if we select december for example):Do you think it's possible to get something like this or am i fighting an uphill baettle with disconnected dates?- amitchandak
Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
If you are using date/month on axis.
Sales YTD =
VAR _max = MAX( 'Disconnected Dates'[Testing Dates])
VAR _min = date(year(_max),1,1)
return
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, Sales_Table[Date]>= _min && Sales_Table[Date] <=Max))
RETURN ResultSales LYTD =
VAR _max = MAX( 'Disconnected Dates'[Testing Dates])
VAR _min = date(year(_max)-1 ,1,1)
return
VAR Result = CALCULATE([Total Sales], FILTER(Sales_Table, Sales_Table[Date]>= _min && Sales_Table[Date] <=Max))
RETURN ResultAs such you should have both independent and joined date table
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI