Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
MiteshY
Frequent Visitor

QTR TARGET, PRESENT WEEK, LAST WEEK

Hello All,

 

My dashboard gets updated daily. I want to show present week, last week data, and in present, last week data i need to show QTD , QTR values along with Target. 

 

All is working fine for current week. But for last week i need a help to create a dynamic target measure, so when present week is there and quarter has ended, in last week it should show previous quarter target values.

 

For eg. Suppose today is fiscal week 14 and previous fiscal week was 13.

 

As quarter has now ended in 13th week , so in previous week it should show values till 13 week and previous quarter. But for current it will be showing quarter 2 and 14th week data.

 

NOTE- I GET QUARTELY GOALS ON QUARTER BASIS, AND FOR OTHER , DATA IS UPDATED DAILY.

 

Thanks.

2 REPLIES 2
Adamboer
Responsive Resident
Responsive Resident

To create a dynamic target measure for last week, you can use a combination of the IF and DATEDIFF functions in DAX. Here's an example of the formula you can use:

Last Week Target = IF( DATEDIFF( MAX('Date'[Date]), MAX('Table'[Date]), WEEK ) = 1, MAX('Table'[Current Week Target]), MAX('Table'[Previous Quarter Target]) )

In this formula, 'Date' is the table that contains the dates, and 'Table' is the table that contains the data you want to show. You'll need to replace these with the actual names of your tables.

The DATEDIFF function calculates the number of weeks between the current week and the week of the data in the 'Table' table. If this number is equal to 1, it means the data is from last week, so the formula uses the current week target. Otherwise, it means the data is from a previous week, so the formula uses the previous quarter target.

You can then use this measure in your visualizations to show the target for last week.

Adamboer
Responsive Resident
Responsive Resident

To create a dynamic target measure for last week, you can use the following DAX formula:

Last Week Target = IF(MAX('Table'[Week])=MAX('Table'[Current Week]), [Current Quarter Target], CALCULATE(MAX('Table'[Target]), FILTER('Table', 'Table'[Week] = MAX('Table'[Last Week]))))

In this formula, we first check if the maximum value of the 'Week' column is the same as the maximum value of the 'Current Week' column. If it is, then we use the 'Current Quarter Target' measure. If not, we use the 'CALCULATE' function to retrieve the maximum value of the 'Target' column for the last week.

To show values till the previous quarter for the last week, you can modify the formula to use the 'IF' function again to check if the last week is in the previous quarter. If it is, then you can retrieve the target values for the previous quarter using a separate measure.

I hope this helps!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.