cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tanat_inc
Helper I
Helper I

Sale of last 7 day of selecting period

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.

tanat_inc_0-1621133349533.png


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

1 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

 

Picture2.png

 

 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

10 REPLIES 10
tanat_inc
Helper I
Helper I

@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
Community Champion
Community Champion

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.

 

Picture1.png

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
)
)
)
 
 
 
 

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

 


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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.

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.


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

@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.

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.

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.


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

@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

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

 

Picture2.png

 

 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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] )
)

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors