Display 6 months data in bar chart for selected filters using slicers with help of DAX !!!
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.Dates!! Not quite sure how to describe this in one line ....but need help
Hi All Am newish to Power Bi and Dax - all self taught at moment, although am working through videos and find these forums really helpful. I have a problem which I just cannot get my head round how to solve..... I am working with school term dates. Have a "helper" table which has start and end dates for each term and also a term report date (which is at some point during a term). This also has a "Quarter" allocated to each term in appropriate order, ie Autumn, Spring, Summer. Have set up a custom date table where I work out what week a date falls into within school year (term starts beginning September); and have defined a Term and Quarter calculated column based on lookup using helper table. So far so good.. I am working with Attendance data which has a column with a statistical meaning of present/absent for each day of each term. I have Many to One relationship between Attendance and Date Table. I need to be able to have a table visual which shows a term and count of absent, count of present, % Attendance for term selected from a slicer but also needs to show previous term's data. I hvae managed to create measures on Date table for: selected term (from slicer) selected quarter (used in order to allow me to identify previous quarter) selected term start date / end date / report date previous term pervious quarter previous term start date / end date / report date All work perfectly, I am happy to say I then use these measures in Attendance table to do a count of absent / present and calculate % Attendance CountAbsent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent",1,0))) CountPresent = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present",1,0))) %PresentTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermEndDate])) All work perfectly Also done for the period up to selected term report date: CountAbsent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]<=[TermReportDate],1,0))) CountPresent-Report = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Present" && Attendance[Mark date]<=[TermReportDate],1,0))) %PresentTerm-Report = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[TermStartDate] && Attendance[Mark date]<=[TermReportDate])) Again these all work perfectly. However I cannot get this to work for previous term (which would be whole of term), I know my measures are correct as I have tested by creating a table which shows me the selected term information (dates) as well as previous term information (dates) I followed same principles for counting absent/previous as above, ie: CountAbsent-Previous = calculate(sumx(Attendance,if(Attendance[Statistical meaning]="Absent" && Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate],1,0))) %PresentPreviousTerm = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(Attendance,Attendance[Mark date]>=[PreviousTermStartDate] && Attendance[Mark date]<=[PreviousTermEndDate])) But the answer I get is zero for the counts and absolutely nothing for the %. Am baffled as to why this will not work when it works on the previous term information table. See attached screenshot. Any suggestions (in idiot speak) would be gratefully received!!!select MIN date of relative date slicer in every row of table
Hi! I have this measure: Measure = VAR FECHA = CALCULATE(MIN('DATA BASE'[DATE]),ALLSELECTED('DATA BASE'[DATE]),VALUES('DATA BASE'[resp x dep 2])) RETURN FECHA and in my table is returning me this: i want to force the measure to only display the values that have information in the MIN date selected in the slicer. Similar as when I select the specific date: I will use this variable for another measure Thank you for the help!!
Help - DAX Measure for Dynamic Chart Title with DrillDown
Dear Experts, I am trying to create the below measure but it is not working. Measure - Test Chart Title = Var MonthPart = SELECTEDVALUE('Time Day'[Month_Year],"Current Month") Var Geography = IF( ISFILTERED('PS Location'[COUNTRY], SELECTEDVALUE('PS Location'[COUNTRY]), "" ) return "Country Wise" & Geography & MonthPart Geography is not returned in this measure. Please help.Divide two dynamical Measures
Hello, I have a P&L Table, sorted by Month. &L Tabel I created the following measures to visualize the data: (note there are Slicers) Measure "Actual" just gives the values, according to category and month. Measure "Numerator" states the costs/incomes in the selected month. Numerator = VAR Cost_of_Material = CALCULATE([Actuals]; FILTER('Financial Category'; 'Financial Category'[Category] = "Cost of Material")) VAR Personnel = CALCULATE([Actuals]; FILTER('Financial Category'; 'Financial Category'[Category] = "Personnel")) VAR other_Exp = CALCULATE([Actuals]; FILTER('Financial Category'; 'Financial Category'[Category] = "other op. Exp.")) VAR other_Inc = CALCULATE([Actuals]; FILTER('Financial Category'; 'Financial Category'[Category] = "other op. Inc.")) VAR Depreciation = CALCULATE([Actuals]; FILTER('Financial Category'; 'Financial Category'[Category] = "Depreciation")) RETURN (Cost_of_Material + Personnel + other_Exp + Depreciation + other_Inc) 3. Measure "Total Output" gives the total Sales in the selected month. Total Output = CALCULATE([Actuals]; FILTER('Financial Category'; 'Financial Category'[Category] = "Sales Revenue")) 4. Measure "% of Sales Revenues" should give the percentage of each Numerator divided by Total Output. % of Sales Revenue = [Numerator]/[Total Output]) // or DIVIDE([Numerator];[Total Output];0) As you can see, the 4. Measure isn't showing my anticipated results. If I enter the static number of Sales Revenues (in the table = 4,836 Mil. €), I receive the correct percentages in the column. I tried several approaches to get the percentage. It seems that I can't put a measure, which only refers to one value, in the Denominator. I won't be able to add the Total Output manually in the Denominator using IF-clauses, since the table will update regularly. Is there any way to divide the Nominators by Sales Revenue?? Thanks in advance
Help needed with dax measure to calculate runningsum value against a time based value
I'm stuck with something that seems simple but has taken me to much time already. Hopefully someone can help me with this. I have a transaction table and a table that contains project information over time. On the report I need the runningsum value of the transacties calculated against a percentage wich is applicable on the project and all based on the slicer selected month value. A simple representation of the case is: Transactions table: Project Date Number A 31/12/2019 100 A 21/01/2020 -10 A 01/02/2020 -10 Projectinfo table: Project Date Percentage A 31/12/2019 10% A 31/01/2019 50% A 28/02/2019 100% Required Result (running sum value * applicable percentage at time of slicer) Slicer = December 2019 =100*10% = 10 Slicer = Januari 2020 =90*50% = 45 Slicer = Februari 2020 =80*100% = 80 Can anyone please help me with my problem? Many thanks in advance.
