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.
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.
I fixed this issue. I had created a measure to display the required columns for the 6 months.
Glad you managed to find a solution, add me in your future questions as @Fowmy So I get a notification.
Thanks
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Balaryan58
Not fully understood but I think you need a solution as shown in this video:
https://www.youtube.com/watch?v=6WU7Ze32Q3w
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 ?
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.
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 |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |