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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sash
Frequent Visitor

Showing current day value and value one week ago on same chart

Hi, 

 

I'd like to show a chart for the last 7 days and show 2 values for each day: current day and value one week ago. The source of the data is a star-modelled data warehouse with a fact table and a related date dimension.

 

Seems like a simple request, but I'm stuck.

 

Here are my measures: 

 

 

Today = DATE(YEAR(NOW()); MONTH(NOW()); DAY(NOW()))

DayDiff = 
SWITCH (
    TRUE ();
    dim_Date[Today] < dim_Date[Date]; -1 * DATEDIFF (dim_Date[Today]; dim_Date[Date]; DAY);
    dim_Date[Today] > dim_Date[Date]; DATEDIFF (dim_Date[Date]; dim_Date[Today]; DAY);  
    0
)

Last7Days = IF(dim_Date[DayDiff]<7 && dim_Date[DayDiff]>=0; TRUE(); FALSE()) 

Impressions = SUM('fact_TweetEngagement'[ImpressionsDiff])

ImpressionsPW = 
CALCULATE(
    fact_TweetEngagement[Impressions];
    DATEADD(dim_Date[Date]; -7; DAY)
)

 

 

I added a bar-chart. To show only last 7 days I set the visual filter to Last7Days=TRUE. I added Impressions and ImpressionsPW measure. The chart shows Impressions measure, but not ImpressionsPW. It seems that the visual filter affects the ImpressionsPW measure so the measure is not returning any values.

 

Here's an example of a modified chart showing last 2 weeks:

 

powerbi_chart.png 

As you can see the ImpressionsPW value is not returning values for dates older than 1 week, because it's out of the visual filter context (7+7...).

 

What am I doing wrong?

 

1 REPLY 1
v-micsh-msft
Employee
Employee

Hi sash,

 

I think the impressionSPW not showing the earlier week data should be caused the the DATEADD function.

By the way, could you please share some data sample so that we could build some texts based on that?

In addition, regarding showing the last 7 days, check the reply from smoupre:

Can I set page filters for "last 7 days"?

Regards

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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