Help with Dashboard

[Anonymous]

Hello everyone, 

I am trying to develop a dashboard, I have main sales table, each entry in this table has a date with only month and year. I want to show the data using cards and slicers on the main visualization. The first slicer is for brands, the second slicer is for month and year and the last slicer is the end scale.

Brand slicer: user can select the particular clothing brand he wants to see the data for

Month_year slicer: user can select the particular month and year he wants to see the data for

Endscale slicer: user can select the timeframe he wants to go back from the particular month and year value he selected from the month_year slicer

For example, the user can select that he wants to see the sales for Nike, for the month of September and year 2022 and he selects the endscale as 6 months, this means he will see the last six months of data starting from September 2022.

These are the two slicer that I have created. The endscale slicer comes from a table that I have created which is not connected to any table. The month_year slicer comes from the Calendar table that I have created which is connected to the main sales table with a column by the same name as month_year. 

This is the calendar table with 3 other calculated columns that I made.  I also made a measure to display the total sales like I described in the Nike example above, this is what the measure is:

testing = 

VAR endscale_selection = SELECTEDVALUE('Time'[Endscale])
VAR y = SWITCH(endscale_selection, "R03", 2, "R06", 5, "R12", 11)
VAR month_selection = SELECTEDVALUE('Calendar'[Month_Year])

VAR start_date = DATE(YEAR(month_selection), MONTH(month_selection),   DAY(month_selection))
VAR end_date =   DATE(YEAR(month_selection), MONTH(month_selection)-y, DAY(month_selection))
VAR x = CALCULATE(SUM(Sales[Dollars]),   
                  FILTER(
                      'Calendar', 'Calendar'[Month_Year]>= end_date && Calendar[Month_Year]<= start_date
                  )
                )
RETURN x

In the above code I am trying to display the sales of a particular brand based on the slicer selection for timframe/endscale, the particular month_year combination and the particular brand. But for some reason the card which holds this measure is just showing a "Can't Display this visual" message. 

I am also attaching the image of my table model for reference(removed some things due to privacy):

Any suggestions would help a lot, as to how can I make it work.

Thanks in advance. 

[Ashish_Mathur · Accepted solution]

Hi,

Conver the Month and year column(s) into a proper Date column in your Sales table.  Ensure there is a proper date column in the Calendar Table as well.  Create a relationship (Many to One and Single) from the Date column in your Sales ata table to the Date column in your Calendar Table.  Drag Month and year from the Calendar Table.  Write these measures:

selected end scale value = selectedvalue(Time[endscale])

Measure = calculate(sum(Data[sale]),datesbetween(calendar[date],edate(min(calendar[Month_year]),-[selected end scale value]),max(calendar[Month_year])))

Hope this helps.

[v-stephen-msft]

Hi @Anonymous ,

Is your problem solved, please? If it is solved, please mark the problem with help as a workaround, more people will benefit.

Best Regards,

Stephen Tao

[Ashish_Mathur · Accepted solution]

Hi,

Conver the Month and year column(s) into a proper Date column in your Sales table.  Ensure there is a proper date column in the Calendar Table as well.  Create a relationship (Many to One and Single) from the Date column in your Sales ata table to the Date column in your Calendar Table.  Drag Month and year from the Calendar Table.  Write these measures:

selected end scale value = selectedvalue(Time[endscale])

Measure = calculate(sum(Data[sale]),datesbetween(calendar[date],edate(min(calendar[Month_year]),-[selected end scale value]),max(calendar[Month_year])))

Hope this helps.

[Anonymous]

@Ashish_Mathur Hi Thanks for the response, is it okay if they are in date/time format?

[Ashish_Mathur · Accepted solution]

You are welcome.  Ideally that field should only have Dates.  However, if the entries in that column have Date and time, then using the INT() function, you should extract Date in another column,.

[Dinesh_Suranga]

@Anonymous 

Hi,

Click on error message then you can see the reson for that.

If you can not fix the error. Please share the error. 

Thank you.

[Anonymous]

@Dinesh_Suranga Thanks for the suggestion, I completely overlooked checking the details of the error!