CUBEVALUE problem

[shophoney]

Hi I have written a CUBEVALUE function and it works fine selecting the month from the slicer. But the issue I now have is that i want the month to date sales. But I dont want to have my staff toggle the month. How can I have it just displace this months values. And when we roll into the next month. Again it would only show September sales.


=CUBEVALUE("ThisWorkbookDataModel","[Measures].[SALES TOTAL NET RETAIL]",Slicer_MONTH1,Slicer_YEAR1,Slicer_CLASS7,Slicer_NAME7,Slicer_TRANS_DATE)

Thanks for reading

 

[Matt Allington]

Are you saying that you want the slicer to show August month to date for this month, and then when the new month starts the measure shows September? Assuming the measure already works correctly and all you need is to change the slicer automatically, the way to solve this problem is to create a Calculated column in your calendar table. The new column should return "current" for the current month and the month name for every other month. You need a formula that can detect the current month in this calc column. The whole idea is that when you refresh the workbook, the calculated column Will change sorry that the current month is flag with the word current. Then you put the slicer on the worksheet and select current. As the calculated column refreshes each month the slicer will update so that it is always the latest month.


I hope that makes sense

 

[shophoney]

Hi Matt the idea sounds perfect. It’s exactly what we’re looking for. Would you happen to have any suggestions on how to create the calculated call him that will return the current month. I have never tried this procedure before but it would be very helpful in our development thanks and all the best

 

[Matt Allington]

I normally write a calc column something like this.


=if(month(calendar[date])=month(today()) && year(calendar[date])=year(today()),"Current Month",Calendar[Month])

 

[shophoney]

Thanks Matt. That totally did the trick. I think it will allow me to finish off this year long project.

To that have helped other. Thank you for helping those in need.