By Sumit Ray
Introduction :- In this article , We will explore how to display last week's data.
We are going to utilize DAX functions to create the Calculated Date Dimension Table and we will be displaying the visualization using the sample data to illustrate the number of tickets, issues, and resolved cases from the past week. Below are the steps for the same: To calculate last week Data
1. Create Date Dimension :- We will use CALENDAR Function for get list of dates which we defined in the Range and along with we are using the NOW() Function in the Range which basically show the Today’s date (Current Date)
Dax Syntax :- Date DImension Table = CALENDAR(DATE(2024,01,01),NOW())
2. Get week number :- WEEKNUM Function show the week number in the listed Year
Dax Syntax :-WeekNumber = WEEKNUM('Date DImension Table'[Date])
3. Create last week's flag as 0 and 1 .
Dax Syntax :-
Last Week Flag =VAR Preweek = CALCULATE(MAX('Date DImension Table'[Week])-1 , ALL('Date DImension Table'))
RETURN
IF('Date DImension Table'[Week]=Preweek , 1,0)
4 . We will use the Date column to make an active relationship between Date
Dimension Table and sample data tables .
5. We can now present last week's issues based on the measures specified, using sample data to create the visualization. 6. We will use the Last week flag column = 1 as a filter on the visual below, which will help us to fetch the last week data. OUTPUT
2. How to display the last week's refresh date using KPI
2.1 We will create a measure using the DAX Function. Dax Syntax :- Last Week Refreshed Date = VAR Maximum = CALCULATE(MAX('Date DImension'[Date]),FILTER(ALLSELECTED('Date DImension'[Last Week Flag]), 'Date DImension'[Last Week Flag]=1))
RETURN
Maximum OUTPUT
Comments