Bart chart showing MTD QTD & YTD on the same x axis

[BethNurse]

Hi, I am trying to show the MRD, QTD & YTD on a single bar chart.

I have calculated each value using a measure changing the calculated offset for month, quarter and year as follow:

AST_Funnel_YTD = CALCULATE(sum(AST_Funnel[Estimated Revenue (Weighted)]), FILTER(all(AST_Funnel),RELATED('Calendar'[CurFiscalYearOffset]) = 0))

I have all three values to be plotted but can't seem to be able to plot them in one graph.

This is the graph I am getting if I choose a clustered chart but it won't allow me to add the proper legend to show the subcategory that generate the value.

When what I would like to have is a combination of these three bar charts utilizing the same y-axis as three series on the same y-axis.

Can anybody offer any help

Thanks

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, please take following steps:

1)Create a table by Enter Data:

2)Create these columns in original table:

Year = 'Table'[Date].[Year]
Year&Quarter = FORMAT('Table'[Date],"YYYY-q")
Year&Month = FORMAT('Table'[Date],"YYYY-MMM")

3)Try this measure:

Measure =
SWITCH (
    SELECTEDVALUE ( Cumulative[Cumulative] ),
    "MTD", [MTD],
    "QTD", [QTD],
    "YTD", [YTD]
)

4)The result shows:

Note that why the values above is the same is that it is because there is no date filter column in this visual's Axis, and you can add the above three date columns to show, such as add [Year] column, and you can see YTD in visual has different values for different year:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[lkoch]

Can I get an updated link to the test pbix file, really trying to replicate this model and am struggling.  Thanks in advance.

[amitchandak]

I did not get it completely.  But With the date calendar and with the following formula you should have it.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))



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"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[BethNurse]

Amitchandak,

Thank you for your reply.

The measure I created work well and return the correct information.

The problem I am having is with visualisation, I want to be able to show a bar graph with column for all three value (MTD, QTD, YTD) using a single Y-axis but as three separate series retaining the abillity add a legend for the sub categories.

Trying to achieve something like the picture below:

Any ideas on how to get there?

Thank you for your help!