Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tips and Tricks
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

amitchandak's avatar
amitchandak
Super User
6 years ago

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.