Display 6 months data in bar chart for selected filters using slicers with help of DAX !!!

[Balaryan58]

Hello Experts,

As I am newb in PowerBI, I just came here to request for the expert advise and direct me to right way. I am trying to achieve the desired result with help of Slicers where user can select Maker and the year month to see the last 6 months including the selected current year-month field.

I am aware that there is no direct way of getting the visualization chart and need to use DAX to achieve it. I have a Product table whose record looks like exactly below listed one. 

Header columns are  1. Date   2. MAKER   3. YearMonth  4. Location  5. Value

1/1/2020	Tesla	202001	Field2	99.70%
1/1/2020	Tesla	202001	Field3	89.00%
2/1/2020	Tesla	202002	Field2	99.00%
2/1/2020	Tesla	202002	Field3	99.80%
3/1/2020	Tesla	202003	Field2	91.00%
3/1/2020	Tesla	202003	Field3	92.00%
4/1/2020	Tesla	202004	Field2	95.00%
4/1/2020	Tesla	202004	Field3	89.00%
5/1/2020	Tesla	202005	Field2	99.00%
5/1/2020	Tesla	202005	Field3	99.80%
6/1/2020	Tesla	202006	Field2	99.80%
6/1/2020	Tesla	202006	Field3	99.90%
7/1/2020	Tesla	202007	Field2	89.00%
7/1/2020	Tesla	202007	Field3	99.00%
8/1/2020	Tesla	202008	Field2	90.00%
8/1/2020	Tesla	202008	Field3	93.00%
1/1/2020	Nio	202001	Field1	99.80%
1/1/2020	Nio	202001	Field4	91.00%
2/1/2020	Nio	202002	Field1	99.80%
2/1/2020	Nio	202002	Field4	97.00%
3/1/2020	Nio	202003	Field1	99.80%
3/1/2020	Nio	202003	Field4	100.00%
4/1/2020	Nio	202004	Field1	99.80%
4/1/2020	Nio	202004	Field4	97.00%
5/1/2020	Nio	202005	Field1	99.80%
5/1/2020	Nio	202005	Field4	100.00%

Also I have a calender table which contains Date, Monthyear, Year fields just to refer it in DAX. When I googled for the hint, all I get is the sum of sales record for 12 months of data. Ultimately, its of only one column. But my requirement is to display 2 columns which was location and value from the above table using the selected value from Slicers. 

For instance, If i selecte 'Telsa' as Maker and year-date as '202008', then I should generate the chart for TESLA from '202003' to '202008' having Location and value bins in the chart.

For each month, I will have 2 bins one for field2 and other for field 3. So in total, we should be seeing 6 pair of bins for the required 6 months range. 

I had coded the measure column to perform the required function in the table and listed below for reference. I had struck in completing it as I am not getting the complete picture to put it in DAX Code.

6 months data =
VAR MaxDate = MAX( 'Calendar1'[Date] )
VAR Date_6Months = EOMONTH( MaxDate, -6 )
VAR Result =
IF (
HASONEVALUE( 'Calendar'[MonthYearNo] ) &&
MAX( 'Calendar'[Date].[Date]) <= MaxDate &&
MIN( 'Calendar'[Date] ) > Date_6Months ,
CALCULATE(
 ??????,   /** Not sure how to list the required fields like 'Location' and 'Value' in this DAX code
FILTER( ALL( 'Calendar1'[MonthYearNo] , 'Calendar1'[Year] ),
'Calendar1'[MonthYearNo] = VALUES('Calendar'[MonthYearNo] ))
),
BLANK()
)
RETURN
Result

Kindly provide your expertise opinion or correct me if i am wrong anywhere.  Any info or help would be appreciated. 

[Balaryan58]

I fixed this issue. I had created a measure to display the required columns for the 6 months. 

[Fowmy]

@Balaryan58 

Glad you managed to find a solution, add me in your future questions as  @Fowmy   So I get a notification. 

Thanks 

[Fowmy]

@Balaryan58 

Not fully understood but I think you need a solution as shown in this video: 

https://www.youtube.com/watch?v=6WU7Ze32Q3w

[Balaryan58]

@Fowmy 

Sure. At present, i am creating the "line and clustered column chart". I got the desired result with the columns. But when I tried to get the line on the column chart, I am getting the current value in the chart instead of unique values for the date range. 

I could not drag that dollar value field into the line value in Visualization page. Instead I created the MEASURE to get the dollar value for the 6 months date range as per requirement. Unfortunately, the dollar value was selected for the selected current date range using Slicers.

I am not sure what mistake I am doing. Could you direct me ?

[Balaryan58]

Thanks for your response. But the problem is not yet resolved. My requirement is to display the column field in bar chart based on the date and month chosen. I am having hard time in displaying the field and I have no clue how to pass the column field in DAX query as its in percentage value. 

I am getting a chart for all the year listed in table irrespective of using slicer and it should work for 6 months.

ASN 6 months =

CALCULATE (

sum('New Metric Table'[Field1]),

DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -6, MONTH )

)

Also its weird that i am getting 4 in 1st row and its keep getting accumulated by 4 in each month. Even if i use SUM or COUNT, it result same. All i wanted to display the value from "'New Metric Table'[Field1]" and had no clue how to do it.

Could you assist where I am doing wrong?