Forum Discussion
Column Bar Chart Split from the same data?
Good morning. I am working on a chart with some crime data. My boss would like a column chart showing crime data for the previous day and the current day, but he would also like to see a comparison of week over week or day over day for a week’s period. My data comes from an SQL query to an external DB and is from 2015 to present. Hence when I place the crime type into the Axis/Value I am unsure how to then split that data into two separate time frames to meet the request for day over day and week over week view.
I am a beginner on this tool so I do not know DAX or other more sophisticated mechanisms to deal with the data, but am willing to explore and learn.
Hopefully this makes sense. Thank you.
Hi Anonymous ,
First, create an unrelated calendar table as slicer.
DimDate = ADDCOLUMNS(CALENDAR("20/04/2020","28/05/2020"),"week", WEEKNUM([Date]))Create measures to filter data from previous week and previous day.
last_day = IF(MAX('Table'[date])<=SELECTEDVALUE(DimDate[Date])&&MAX('Table'[date])>=SELECTEDVALUE(DimDate[Date])-1,1,0)Last Week = VAR CURRENT_WEEK = SELECTEDVALUE(DimDate[week]) VAR CURRENT_YEAR = YEAR(SELECTEDVALUE(DimDate[Date])) RETURN IF(MAX('Table'[week])<=CURRENT_WEEK&&MAX('Table'[week])>=CURRENT_WEEK-1&&YEAR(MAX('Table'[date]))=CURRENT_YEAR,1,0 )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous , you need to use date table for that So you can have a measure, which can be summarized based on date or week you selected
example
sales =SUM(Sales[Sales Amount])
//Day behind for yesterday
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
//7 Day behind for same week day
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))
You can take a diff for change
Diff = [sales]-[Day behind Sales ]
For Week refer my blog : https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
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.Hi Anonymous ,
First, create an unrelated calendar table as slicer.
DimDate = ADDCOLUMNS(CALENDAR("20/04/2020","28/05/2020"),"week", WEEKNUM([Date]))Create measures to filter data from previous week and previous day.
last_day = IF(MAX('Table'[date])<=SELECTEDVALUE(DimDate[Date])&&MAX('Table'[date])>=SELECTEDVALUE(DimDate[Date])-1,1,0) Last Week = VAR CURRENT_WEEK = SELECTEDVALUE(DimDate[week]) VAR CURRENT_YEAR = YEAR(SELECTEDVALUE(DimDate[Date])) RETURN IF(MAX('Table'[week])<=CURRENT_WEEK&&MAX('Table'[week])>=CURRENT_WEEK-1&&YEAR(MAX('Table'[date]))=CURRENT_YEAR,1,0)Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
Yes, you need to create a measure and apply it to the visual level filter as the picture show.
You can refer to .pbix for more details.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
