Forum Discussion

jamalq123's avatar
jamalq123
Advocate III
9 years ago
Solved

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 wa...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
jamalq123
Advocate III
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    jamalq123
    Advocate III
    9 years ago

    Excellent, It is working well.

     

    Thanks a lot