Sale of last 7 day of selecting period

[tanat_inc]

Hi community,

I have to plot barchart like this one to show sale of last 7 days from today by limit date filter for x-axis.

What should I do to make it show the sale of last 7 days of selecting period, instead of last 7 days from today.

Thanks in advance

[tanat_inc]

@Jihwan_Kim  Thanks for your time ans patient

Expected result will look like the  barchart on first post.

When I select March 2020, I expected to see the plot of 25/3/2020 - 31/3/2020 instead of 1/3/202 - 31/3/2020.

[Jihwan_Kim]

Hi, @tanat_inc 

I suggest having a separate slicer table (date table) like the below picture.

Please check the below picture and the sample pbix file's link down below.

Sales =

VAR slicerdate =

MAX ( Slicer[Date] )

RETURN

CALCULATE (

SUMX ( Sales, Sales[Qty] * Sales[Price] ),

KEEPFILTERS (

FILTER (

ALL ( Dates ),

Dates[Date] > slicerdate - 7

&& Dates[Date] <= slicerdate

)

)

)

https://www.dropbox.com/s/ewomx16gerde251/tanatinc.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[tanat_inc]

Thanks for suggestion, it's work.

However, it will not sync with other visual and drill through function this way.

Is it possible to work with the real date table, or at least make it link to that slicer date table ?
To make it synchronize with other visual.

[Jihwan_Kim]

Hi, @tanat_inc 

Thank you for your feedback.

In a general situation, it is possible to work with the real dataset. However, I do not know how your data model looks like, and I cannot tell how to fix the measure without seeing your data model. 

Share your pbix file's link here then I can try to look into it.

[tanat_inc]

@Jihwan_Kim  Thanks for your patient; Below is link to my sample pbix. Data duration in dummy file is 2020-01-01 to  2020-06-01.

I am looking to create measure like "Total Revenue Last Selected Month" and  "Total Revenue Last Selected Week"  .

I plan to allow user to choose date period freely and most visual will show the data of the whole period, except some visual will limited to last week / last month of selected period.

https://www.dropbox.com/s/2e8tjw6r4w49qp3/dummy_2020.pbix?dl=0

Thanks very much.

[tanat_inc]

An issue about dealing with date in my data set is that : Total Revenue must be reference to bill_date, while Visit count will reference to visit_date. Both date usually not the same date.

[Jihwan_Kim]

Hi, @tanat_inc 

Thank you for sharing.

Sorry that I quite do not understand your question.

Could you please let me know how you want to see the result?

In the pbix file that you shared, I think you already have all the measures you need.

[tanat_inc]

@Jihwan_Kim  Thanks for your time and patient

Example of expected result is like the bar chart on the first post.

However I want the barchart to always limit to the last 7 days, regarding how long selecting period choosen.

Ex: If I choose the whole quarter 1 of 2020, I expects bar chart to display revenue of 24/3/202 - 31/3/202 daily instead of 1/1/2020 - 31/3/2020

Thanks

[Jihwan_Kim · Accepted solution]

Hi, @tanat_inc 

Thank you for your feedback.

Please check the below picture and the pbix file's link down below. I hope I understood correctly.

I wrote two measures and each measure was named as  XXXXXXX V2

https://www.dropbox.com/s/2105015llc7i4ka/dummy_2020.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[tanat_inc]

Thanks man, you save my day.

For those who are interest in the solution, below is the code by @Jihwan_Kim  :

Total Revenue V2 =

VAR lastdateselected =

CALCULATE ( MAX ( d_date[Date] ), ALLSELECTED ( d_date ) )

RETURN

CALCULATE (

SUMX (

FILTER ( bill_info, bill_info[bill_date] >= lastdateselected - 7 ),

bill_info[net_amt]

),

USERELATIONSHIP ( d_date[Date], bill_info[bill_date] )

)