Reply
Regular Visitor
Posts: 44
Registered: ‎10-05-2016
Accepted Solution

Visualization for a period

 

I have a date slicer in a sheet when select to any month , we can see date wise sales and same period comparision. For the date wise sales i use Beyondsoft Calendar which is very interesting

I want to see the trend of the last 6 months. I use the following measure and it produce the visualization as per my requirement. Th emeasure is given below:

Salestrens = CALCULATE([Total Sales],DATESBETWEEN(dDate1[EoMonth],DATE(1997,8,31),DATE(1998,1,31))).

 

But I am using here hardcoated date. I want to make it dynamic. I tried to use datesinperiod, dateadd, max, lastdate and values function but could not get the result.

 

Downloab pbix file from here.

https://drive.google.com/file/d/0B5eKgvOGu5MLWGlXd1pWYmVzVzQ/view?usp=sharing

 

Embed cose is also here:

https://app.powerbi.com/view?r=eyJrIjoiMTczMDcyMTYtZmRjZS00ZjlmLWI4YjUtZDI3ZmY1MTQzZmNhIiwidCI6ImY5Y...

 

Please suggest the solution.

 


Accepted Solutions
Highlighted
Community Support Team
Posts: 7,711
Registered: ‎08-14-2016

Re: Visualization for a period

Hi @jamalq123,

 

It seems like you have misunderstood for my formula. Actually, you need to select two date on slicer as the source of measure.

 

For my formula, it support to choose multiple value and get the dynamic range based on slicer.

 

According to your description, it sounds like you need to get the static range(6 month), right?
If this is a case, you can try to use below formula:

Salestrens 2 = 
var selectDate=IF(HASONEVALUE(dDate1[EoMonth]),VALUES(dDate1[EoMonth]),LASTDATE(ALLSELECTED(dDate1[EoMonth])))//get the max date form slicer if you select mutiple value
return
CALCULATE([Total Sales],DATESBETWEEN(dDate1[EoMonth],DATE(YEAR(selectDate),MONTH(selectDate)-6,DAY(selectDate)),selectDate))

6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post


All Replies
Community Support Team
Posts: 7,711
Registered: ‎08-14-2016

Re: Visualization for a period

HI @jamalq123,

 

I think you can try to use below formula to let result dynamic changes:

Salestrens 2 = 
CALCULATE([Total Sales],DATESBETWEEN(dDate1[EoMonth],FIRSTDATE(ALLSELECTED(dDate1[EoMonth])),LASTDATE(ALLSELECTED(dDate1[EoMonth]))))

1.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Regular Visitor
Posts: 44
Registered: ‎10-05-2016

Re: Visualization for a period

Thanks for sharing your idea,

 

Last date worked well but first date is not ok in my case. I want to see last 6 month data, when I use firstdate it gives all the data from month 1. I use thye following measure and it works well but first date is still hard coated.

 

Salestrens = CALCULATE([Total Sales],DATESBETWEEN(dDate1[EoMonth],DATE(1997,12,31),LASTDATE(ALLSELECTED(dDate1[EoMonth]))))

 

If any solution for the first date I shall be thankful.

 

Regards,

 

Jamal Qamar 

Regular Visitor
Posts: 44
Registered: ‎10-05-2016

Re: Visualization for a period

Snap.png

 

 

 

Highlighted
Community Support Team
Posts: 7,711
Registered: ‎08-14-2016

Re: Visualization for a period

Hi @jamalq123,

 

It seems like you have misunderstood for my formula. Actually, you need to select two date on slicer as the source of measure.

 

For my formula, it support to choose multiple value and get the dynamic range based on slicer.

 

According to your description, it sounds like you need to get the static range(6 month), right?
If this is a case, you can try to use below formula:

Salestrens 2 = 
var selectDate=IF(HASONEVALUE(dDate1[EoMonth]),VALUES(dDate1[EoMonth]),LASTDATE(ALLSELECTED(dDate1[EoMonth])))//get the max date form slicer if you select mutiple value
return
CALCULATE([Total Sales],DATESBETWEEN(dDate1[EoMonth],DATE(YEAR(selectDate),MONTH(selectDate)-6,DAY(selectDate)),selectDate))

6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Regular Visitor
Posts: 44
Registered: ‎10-05-2016

Re: Visualization for a period

Excellent, It is working well.

 

Thanks a lot