Clustered Column Chart with same period last year

[Anonymous]

Hi, I'm not getting the behavior I'm expecting using a count of a records and the count from last year on a clustered column chart. The desired behavior will give me the values for each inrement on my x axis for both normal time and previous year (See first image for visualisation as a table). However when I do it my filter excludes values based on their orginal time, resulting in a visualation as a table in image two. Not sure what I'm missing. I have expected behavior in a different report but can't find the difference. Any help would be appreciated. If more info is needed from my end let me know. 

  

Here's the forumlas: 

PYClaimCount = calculate(count(DriverFact[C&K Claim Number]),SAMEPERIODLASTYEAR('Calendar Table'[Day]))

Or 

PYClaimCount = calculate(count(SAFETYDATABYDRIVER[Claim_Number]),SAMEPERIODLASTYEAR('CALENDAR'[Day]))

First one is from the report I'm redoing, Second is from mine. 

Sample Data: 

https://github.com/TheKLegacy/uploads/blob/master/calendar.csv 

https://github.com/TheKLegacy/uploads/blob/master/sampleaccidents.csv 

[Ashish_Mathur]

Hi,

How does one download that file?  Also, why are entries in the Claim number showing up as dates?

[Anonymous]

@Ashish_Mathur If you don't know how to use git then you can download a zip by clicking 'code' (in green) here https://github.com/TheKLegacy/uploads.

The values in claim number aren't important, we are getting the count of them. Let me know if you have any other questions. Thanks for the help. 

[amitchandak]

@Anonymous , Yet to check your Data. But for the same period last year. I prefer a trailing measure with Date calendar

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

Or, see if this can also work

CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

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]

@amitchandak 

Thanks for the reply. I think the core of my problem is how the filter is effecting my measure of counts for same period last year. Do you know any options in power bi that might explain this behavior? (See my last two posts including screenshot for more details)

[Ashish_Mathur]

Hi,

Download the PBI file from here.

Hope this helps.

[Anonymous]

Yeah changed to to the visualisation I need and it's exactly what I want. Do you know what I was doing wrong? 

[Ashish_Mathur]

No, i have not delved into understanding your formula.  If my previous reply helped, plese mark it as Answer.

[Anonymous]

Unfourtenently It doesn't seem to work in my report. The Claims measure is fine but the same period last year one has the same behavior. @Ashish_Mathur 

[Ashish_Mathur]

I really cannot say why it is not working unless i see your PBI file.

[Anonymous]

@Ashish_Mathur I can't share the report there is too much senitive data in it. I need to know why the slider is filtering with last year measure with the date used to calculate it and not the date it's being displayed for. I must be missing an option in power bi somewhere. I don't beleive it's a problem with either of our forumlas. 

[Greg_Deckler]

@Anonymous - Can you share sample data and formulas/settings for filters. Data as text in a table so that it can be easily copied.

[Anonymous]

@Greg_Deckler  I've updated the OP. The filter is just a slider with a date range from 1/1/2019 to 3/15/2020. I want to have both current count and last years count for the entire range but nothing outside of it. X axis is just year and month.