Clustered Bar Chart to compare this months data with the last?

[Anonymous]

Hello all,

I use several clustered bar charts to display various bits of monthy data, but I realise that data would be far more worthwhile if I could compare that data with the previous months, and maybe even use a line plus stack visual to show the average too. 

Does anyone have any suggestions on how I could do this?

[v-jayw-msft]

Hi @Anonymous ,

Please check below measures.

month-1 = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),MONTH('Table'[date])=MONTH(SELECTEDVALUE('Table'[date]))-1))

month-2 = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),MONTH('Table'[date])=MONTH(SELECTEDVALUE('Table'[date]))-2))

 

Best Regards,

Jay

[amitchandak]

@Anonymous , may be you can use clustered line visual.

and try time intelligence with date table

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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))


	


diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

The big issue is that the previous month's numbers are not stored in the data.

As an example we have worstation charts that show the age of machines, and as we remove machines from the AD, the number of PCs purchased in 2015 is going down, but as the data is coming live from the AD, I am unsure of how to capture this and keep it.