Visualization for a period

[jamalq123]

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.

[Anonymous]

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

Regards,

Xiaoxin Sheng

[jamalq123]

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 

[Anonymous · Accepted solution]

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

Regards,

Xiaoxin Sheng

[jamalq123]

Excellent, It is working well.

Thanks a lot

[jamalq123]