Sort Order issue
I created a data table so my visuals will sort properly, but it appears about 239 of the answers are unique in such a way its causing the selection to sort by not to work. When I check the data I can see all the unique free text answers result in a null value for the sort order, so the error message makes sense im trying to rack my brain how to best fix this I know I could replace the Answers with a numeric number to force them in a specific order but that seems like a chest, is there a way to make this work?
Custom Comparison Period calculations not working correctly in visuals with dates
Hello, i have a dashbaord that is using a comparison date table, the configuration is such that Im able to select any custom dates to compare my current date range with. Im then calculating % increase/decrease based on the 2 date ranges selected. This seems to be working fine for visuals where the measure isnt broken down by a date dimension, however when i have visuals such as the following: For the comparison period it shows me the same value across different months. I understand this is because im using the months from the primary date dimension table but ideally, I require for this to compare as follows for the above example: compares oct 2024 to january 2024, nov 2024 to feb 2024, and dec 2024 to nothing since in the comparison period only 2 months are selected. However, currently its comparing each month against the total of jany-feb 2024 which is not what i want. Ive tried different work arounds but cant seem to arrive at a solution here. Any help would be appreciated please. Currently im using a second date table and am using an inactive relationship and the following DAX for the comparison period calculations: (sample) pageviews_previous = CALCULATE(SUM(total_pageviews_by_date[screen_page_views]),ALL(dimdate),USERELATIONSHIP(total_pageviews_by_date[date], dimdate2[date]))
making a comparison between two years for Accounts Receivable
Hello, I am trying to make a table which displays how many invoices are still open at the end of each month in PowerBI. I got the task to create a visualisation based on from an ERP software my company is using since they want more control over the visualisation. see following picture: The diagram shows how much is still open at the end of the month Jan, Feb, Mar, and so on during 2 different years. I have created a table that includes Debtor code and name, Invoice date, Payment date, and amount. Then I created a matrix based on the data available: With the following formula: Sum Debits = SUM('DebtorsInvoices’[Amount]) Which displays the total value correctly. For example I take code 100034 and on January. When I try doing it in excel, I see these values amounted to 95.945: However, what I want is the snapshot amount of open invoices during a specific year. For example, I want to see the amount of unpaid invoices during January 2023. In Excel, I would custom filter the invoice date to before 01/02/2023 and payment date either greater than 31/01/2023 or blank which will filter the invoice like this: Which equals to 20.282. How can I achieve this in PowerBI? I tried using the formula: OpenInvoicesByMonth = VAR _EndOfCurrentMonth = MAX('DateTable'[Date]) RETURN CALCULATE( SUM('DebtorsInvoices’[Amount]), FILTER( 'Debiteuren mutaties', [Invoice Date] <= _EndOfCurrentMonth && (ISBLANK([PaymentDate]) || [PaymentDate] > _EndOfCurrentMonth) ) ) But, that formula displays the result only for the following invoices: How can I include the paid invoices as well? Thank you for your assistance, and please bear with me as I am still new to PowerBI.
Measure calculation divide function not work
Hello Everyone , I have created 3 simple measure , unfortunatley from 3 of 2 is working and 1 is not work well anyone have any suggestion ? here i am sharing 3 measure . with that i also share what my need , you may also refer me if something difrent method for achive my aim 1st measure Total Name Count = CALCULATE( COUNTROWS('Move Insq'), 'Move Insq'[MonthIndex] IN VALUES('CustomTable'[MonthIndex]), 'Move Insq'[Year] IN VALUES('CustomTable'[Year]), 'Move Insq'[Property] IN VALUES('CustomTable'[Property]), 'Move Insq'[First Name] <> BLANK() ) this measure count row each names and i used this on my matrix visueal value for see each employee count , which very well working 2nd measure is Total Move insq percent = VAR NumMonthsSelected = COUNTROWS(VALUES('CustomTable'[MonthIndex])) VAR MoveInsWithCustomData = FILTER( 'Move Insq', 'Move Insq'[MonthIndex] IN VALUES('CustomTable'[MonthIndex]) && 'Move Insq'[Year] IN VALUES('CustomTable'[Year]) && 'Move Insq'[Property] IN VALUES('CustomTable'[Property]) && 'Move Insq'[First Name] <> BLANK() ) RETURN DIVIDE( COUNTROWS(MoveInsWithCustomData), NumMonthsSelected, 0 -- Specify the value to return if the denominator is zero ) this measure i used on advance 123 card for see total row and its also working well. show me correct value now 3rd measure is this NameCountRatio = DIVIDE( [Total Name Count], [Total Move insq percent], BLANK() ) with this measure i want to do divide name count value / with total move insq percent value count this measure is trouble for me because its returning 100 % for all row . might be total name count measure and total percent count doing same calculation on first part which is count row then i add do divide with selected month on total move ins1 i need to fix this any how i am too much tired playing arround in this smaller thing i all share screen shots for better understandPower BI chart interactions
Hi everyone,I am a newbie to Power BI and I have two charts, a line chart and a pie chart. I want to ask how to make the line chart show the information of the week within the date I clicked on the pie chart?For example, if I click on 1/7, the line chart should show the data from 1/1 to 1/7.I have searched many articles but I haven't found what I want. Please help me.DAX Help: Measure of Working Minutes between two times
Howdy there. I'm stuck. I'm trying to build a DAX measure that will calculate the working minutes between two datetime objects but just can't figure it out. Has anyone solved this before? I feel like it should be easy, I just can't wrap my head around the solution. Below is my attempt, but it's returning working minutes for the entire day, not just between the start and end time for each day: For the above records, here's what I expect the value to show: Id CreatedTime postedTime workingMinutes 0700-1630 113670 2024-02-27 11:55:00.000 -06:00 2024-02-28 10:06:36.000 -06:00 461 113720 2024-02-28 06:59:00.000 -06:00 2024-02-28 09:09:32.000 -06:00 129 113672 2024-02-27 11:56:00.000 -06:00 2024-02-27 12:08:16.000 -06:00 12 113662 2024-02-27 10:57:00.000 -06:00 2024-02-27 11:14:24.000 -06:00 17 113601 2024-02-26 15:31:00.000 -06:00 2024-02-27 07:49:56.000 -06:00 108 113361 2024-02-22 11:53:00.000 -06:00 2024-02-26 07:31:38.000 -06:00 878 113359 2024-02-22 11:44:00.000 -06:00 2024-02-22 14:53:08.000 -06:00 189 113340 2024-02-22 09:58:00.000 -06:00 2024-02-22 14:33:42.000 -06:00 275 113392 2024-02-22 13:19:00.000 -06:00 2024-02-22 13:21:34.000 -06:00 2 113195 2024-02-20 13:02:00.000 -06:00 2024-02-20 13:14:18.000 -06:00 12 113142 2024-02-20 09:02:00.000 -06:00 2024-02-20 10:12:01.000 -06:00 70 Working Hours are typically defined as 0700-1630 CST. Here's the DAX I was trying so far, but I know it's, to an extent, not even directionally accurate for how I'm supposed to do it. (In the above screenshot, I expect the Total line to be the SumTotal of each individual Load Id value.) _m_CreatedPosted_Time_cgpt = var createdTime = min(####Data[createdTime]) var PostedTime = min(###LoadPostingID[PostedTime]) RETURN -- Calculate the total working minutes between StartDateTime and EndDateTime SUMX ( -- Generate a row for each date between StartDateTime and EndDateTime CALCULATETABLE ( Calendar_DateRangePicker, DATESBETWEEN ( Calendar_DateRangePicker[DateId], createdTime, PostedTime ), Calendar_DateRangePicker[IsBusinessDay####] = TRUE () ), -- For each date, calculate the working minutes based on business hours IF ( Calendar_DateRangePicker[DateId] = createdTime && HOUR(createdTime) < hour(Calendar_DateRangePicker[StartTime]), // If createdTime is before 07:00 IF ( HOUR(postedTime) >= hour(Calendar_DateRangePicker[EndTime]), // If postedTime is after or at 16:30 DATEDIFF(Calendar_DateRangePicker[StartTime], Calendar_DateRangePicker[EndTime], MINUTE), DATEDIFF(Calendar_DateRangePicker[StartTime], postedTime, MINUTE) ), IF ( Calendar_DateRangePicker[DateId] = postedTime && HOUR(postedTime) >= hour(Calendar_DateRangePicker[EndTime]), // If postedTime is after or at 16:30 DATEDIFF(Calendar_DateRangePicker[StartTime], Calendar_DateRangePicker[EndTime], MINUTE), DATEDIFF(Calendar_DateRangePicker[StartTime], Calendar_DateRangePicker[EndTime], MINUTE) ) ) ) I do have a Calendar table which includes the Start and End working time for each day, and indicates if a day is a Business Day. (The business hours are generally 0700-1630.) Here's the relevant tables from the data model: I feel the answer in here is something with a Calculated Table, but I can't figure out how that is supposed to work. *After I get the answer I want here, I'll then get the Count of values so I can run the Average. I do want to have a measure that's just Totals, though, so I could use that for other things if needed, for why I'm not trying to get the Average directly in this DAX. Appreciate any help you can provide! *This is a crosspost to the below Reddit Post: https://www.reddit.com/r/PowerBI/comments/1b9608h/dax_help_measure_of_working_minutes_between_two/
How can i calculate hisotrical exchange rates with a calendar table
Hey, I work with data about workers on projects. A key stat is the monthly headcount, workers active each month, that we can derive from the workers start/end dates and a calendar table. Another key stat, based off the HC formula, is the average pay rate of the workers that are active each month. I have a pre-prepared .pbix that i can share where I've included this formula, it works fine, except the workers are in different countries so have different currencies. And, the currency fluctuates each month. How can i create a dynamic solution where the dax measure will look at the month & currency being reported on the bar chart and find the corresponding month & year on the factCurrency table and calulate the bill rate to USD as per the exchange rate?
