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 understandDAX 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?Duration variable on Y-Axis
Hello, I’m trying to figure out a formula to change my “Duration” variable which is set as a time variable to something numeric that is still readable as hours/minutes to represent on a Column chart on the Y-axis. I believe Power BI can’t do this because the time variable is text when placed into a visualisation. I first formatted my variable to what you see in the “Duration” column, but every formula/ measure I’ve tried has failed. I’ve attached two screenshots. In the column chart I want my duration variable to reflect the actual time not just as a count. Thanks for any help, much appreciated 🙏🏼
Inaccurate Cumulative Sum
Hi, having trouble getting the cumulative sum accurate. Filter issue? Setup: Two tables: 'Rerun'[RejectCodeID];[Units] and 'Scrap'[RejectCodeID]; [Units]. One to many: Dimension table 'RejectCode'[RejectCodeID] The Dax measure I use calculates the cumulative sum of rejects incorrectly after 116. It sould been 35, 57, 77, 93, 105, 117, 118, 119, 120, 121, 122. Cumulative Rejects = VAR TotalRejects = [Reruns] + [Scrap] Return SUMX( FILTER( SUMMARIZE( ALLSELECTED('RejectCode'), 'RejectCode'[RejectCodeID], "Rejects", [Scrap] + [Reruns] ), [Rejects] >= TotalRejects ), [Rejects] ) Any ideas on what can be wrong? Thanks!
Invoice Checking for automation purposes
Hi PBI Community I want to create a DAX measure which returns a "✓" when the invoice amount posted is equal to the same as the previous journal entry posting. and a "✗" when the invoice amount is different. It needs to be conditional on the Amount being posted to the same G/L Account ID and the same Vendor Name. I made the following, but it doesn't consider the element of previous journal entry posting or whether it is the first posting for that Vendor and related G/L Account ID. Automation Check = if(ISBLANK([TY (EUR)]), blank(), IF(CALCULATE([TY (EUR)] / [€ Avg.]) = [# Postings], FORMAT("✓", "Green"), FORMAT("✗", "#Red") )) How could I change it so that the measure returns "x" when it is first postings as well, and not only compares total amount / avg. posting = count of postings. The matrix visualization looks like this right now:DAX Formula and Measure Question
Hello everyone! My data is depicted below (1st picture). It is pretty simple, I created the Total Subtasks Completed and the % of PP Progress columns. The total subtasks completed is the sum of the all the different subtask result columns, and the % of PP progress uses the values in the Total Subtasks completed columns divided by 17 because thats how many different subtasks there are. On the second picture I attached, I made a quick table visual which shows how many subtasks and their % of PP progress a person has. I was looking to create a measure or column which can bucket the % of PP Progress into a 25% bucket. Such as those between 0 and 25% are in 1, 26-50% in another, 51-75% in a third, and lastly 76-100%. Is there a DAX Calculation I can use to make this successful?
