Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Please suggest the solution.
Solved! Go to 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
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
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
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
Excellent, It is working well.
Thanks a lot
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
76 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |