Show data for exactly one week ago

[ca_cs]

Hello,

I'm pretty new to Power BI, so I'd appreciate some advice.

I'm pulling some analytics where I get hourly polls for how many logs were generated for different servers. I'd like to be able to show the logs for the latest polling period and then for exactly 24 hours ago (or exactly one week ago). Below is a link for sample data and I've created a date table using the following code:

date = 
VAR DT = GENERATESERIES (DATE (2022, 01, 01), NOW (), TIME (01, 00, 00))
RETURN
    ADDCOLUMNS(
        DT,
        "Year", YEAR ( [Value] ),
        "Month", MONTH ( [Value] ),
        "Day", DAY ( [Value] ),
        "Day Name", FORMAT ( [Value], "DDDD"),
        "Day of Week", WEEKDAY ( [Value] ),
        "Week of Year", WEEKNUM ( [Value] ),
        "Start of Month", DATE (YEAR ( [Value] ), MONTH ( [Value] ), 1),
        "End of Month", EOMONTH ( [Value], 0),
        "YearMonth", INT (FORMAT ( [Value], "YYYYMM")),
        "MonthYear", FORMAT ( [Value], "MMM-YY"),
        "Quarter", QUARTER ( [Value] )
    )

Sample Data: https://1drv.ms/x/s!AumWVEO00NBUnQqav8GjAAroKjAg?e=TuWfDW 

I'd like to have this information so I can make a clustered bar chart that shows how many logs were collected today, and also how many logs were collected at 24 hours ago (or one week ago). I've only included about ten days of sample data.

Any ideas/suggestions on how this could be achieved?

[amitchandak]

@ca_cs , You should use the calendar function

example

Calendar = Addcolumns(calendar(date(2020,01,01), today()  ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...

With help from the calendar marked as date tabled joined with your date to the table

week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))

Also

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[ca_cs]

Hi, thanks for the response. I gave this a shot but it didn't work.

I think this is because my data is in one hour time blocks. If the current time block is 12/10/2022 1100, I want to be able to see what happened at 5/10/2022 1100. Importing the calendar code you gave me, plus the measure, I can get a result. But I don't want a sum of the days data, I want a sum of the hour. Using my date calendar I've linked above, which does create an hourly calendar, and which has the relationship set, it gives me this error:

MdxScript(Model) (5, 63) Calculation error in measure 'metrics'[one_week_ago]: A date column containing duplicate dates was specified in the call to function 'DATEADD'. This is not supported.

I assume that this is because it only looks at the date, not the time as well? Is there any way to get this working so it goes back to last week, but also last week to the hour?