How to get counts of 14 days ago

[padfootkk]

Hello Everyone,

Now I have two tables, one for items, one for calendar. The two tables are related by dates. An simple example of my data:

ItemTable:

ItemId	Date
122	1/7/2019
456	1/10/2019
789	1/15/2019
565	1/21/2019
124	1/22/2019
498	1/29/2019
135	2/1/2019

Calendar:

Date	BiWeek
1/7/2019	BiWeek1
1/10/2019	BiWeek1
1/15/2019	BiWeek1
1/21/2019	BiWeek2
1/22/2019	BiWeek2
1/29/2019	BiWeek2
2/1/2019	BiWeek2

(1/7/2019-1/20/2019 is BiWeek1; 1/21/2019-2/3/2019 is BiWeek2)

The Calendar contains a calculated column that stands for BiWeek (Two weeks iteration). Now I want to use BiWeek as a date slicer, and use measures to show the count of items in that selected BiWeek and previous BiWeek. The problem is I can't get the previous BiWeek count, I tried to use PrevCount = CALCULATE([CountMeasure],DATEADD(Calendar[Date],-14,Day)) to get the previous BiWeek count, but it didn't work. So does anyone have ideas about this?

For this Example, if I choose BiWeek2 in the Slicer, what I want to get is:

CountMeasure	PrevCount
4	3

Thanks! 🙂

[mwegener · Accepted solution]

Hi @padfootkk ,

did you mark your date table?

https://docs.microsoft.com/en-us/power-bi/desktop-date-tables

You may download my PBIX file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

@padfootkk 

Is this your expected result?

count = CALCULATE(COUNTROWS(Sheet4),ALLEXCEPT(Sheet4,Sheet4[BiWeek]))

Previouscount = CALCULATE(COUNTROWS(Sheet4),ALLEXCEPT(Sheet4,Sheet4[BiWeek]),DATEADD(Sheet4[Date],-14,DAY))

Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[amitchandak · Accepted solution]

Do you want data of day exactly 14day before or 14 days data 14 days before?  It seems like the first option. Make calendar is marked as date

for the second one

Rolling 14 before 14 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],(dateadd(Sales[Sales Date],-14,DAY)),-14,DAYS))

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

You can also treat that custom time period, please refer

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[mwegener · Accepted solution]

Hi @padfootkk ,

did you mark your date table?

https://docs.microsoft.com/en-us/power-bi/desktop-date-tables

You may download my PBIX file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[padfootkk]

Hi @mwegener ,

I didn't mark the date table, because the table was generated based on date column in the Item table, and there are lots of duplicate dates with different time.

I'm now trying to create a new calendar table that has unique dates and marked it as the date table. However, there is no date hierarchy on the date column in the calendar table. Do you know why is that? I also checked your file, the Date column in Calendar table don't have date hierarchy, neither.

Thanks!

[mwegener]

Hi @padfootkk ,

if you create your own data table, you design the hierarchy according to your needs.

Look at this.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.