Datediff of several periods that fall within a range
Hi! I've been trying to get the total of days from different periods that fall within a range. I have a table with PERHH_PERSO (key for worker), with several PERHH_CONTRATO (number of contract), each with a PERHH_FEC_INI (start date) and PERHH_FEC_FIN (end date). For each contract, I establish a range from the Start_date to +364 days, in a calculated column (Day_end_period). I also have a calculated column for the Datediff of the actual contract (Actual_ctr_days) What I need is to count all days from all contracts of that same person (PERHH_PERSO) that fall within that range (from PERHH_FEC_INI to Day_end_period) Here is an extract of the table, with just one person selected: I'm not getting the logic of the calculation here... have tried several approaches but none is working. Help will be much appreciatted. Thanks!
Remove Table Filters While Using Virtual Table Measures
Hello! I'm currently trying to return the max value from a virtual table that I will use in another virtual table, along with removing the table filters when I place this max value in a table visual. Specifically, the 'MAX Efficiency Score' measure will be utilized in the 'Efficiency Score' measure (see 'var table7' in the 'Efficiency Score' measure). Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var table7 = ADDCOLUMNS(table6, "FinalScore", [Score]/[MAX Efficiency Score]) var table8 = SUMX(table7, [Score]) return table8 MAX Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var final = ADDCOLUMNS(table6, "MaxValue", MAXX(table6, [Score])) return CALCULATE(MAXX(final, [MaxValue]), ALL('Fuel (Traveling)')) My current issue is that I can't get the 'MAX Efficiency Score' measure to return the max score of 79.58 (in the case below) for each row of my table visual below. For background, the table visual below contains the same fields (Driver Fleet Name and DriverName/ID) that are used in the SUMMARIZE functions above. I also set the dashboard up so the 'Efficiency Score' and 'MAX Efficiency Score' values dynamically update when the date slider is used, which is why I referenced the 'Combined Calendar[Date - Copy] field in the measures above. I'd really appreciate any help I could get on this issue. Thanks!
Include only articles that have moved consecutively during selected months
Hi everyone, This is my first post here, as I have only recently started working with PBI (I'm coming from a Qlik world, but that might change). With that in mind, I hope that I'm able to formulate my needs as clear as possible. So, I have a problem that I've been stuck for quite some while now. Intro I want to analyze the average sales price development on a monthly basis. The requirements are 1) Calculate the average price per company, article, and year-month; then sum these values up (it's specific to our situation); 2) Only analyze articles that have moved consecutively during the time period that is selected by the user, i.e. if I select year 2021 and months jan-sept, I need to dynamically include only articles that have moved at least once per month during 2021 jan-sept. Month measures For this I first deciced to calculate how many months are available in total (with a minor tweak to remove the current month under certain conditions), a number that should depend only on the time selection: Then, I calculate the months that are available, accepting all filters: Lastly, I create a new measure that is a straight comparison between above two measure, i.e. a boolean that I mean to use as a filter later: So far so good, in as much as these measures verifiably work. Article filter Now comes the difficult part: creating a filter of articles where this latter condition is true. I have tried many things that don't work, so this is what I have at the moment: This works - however, it is extremely slow. As you may have already realized, the FILTER(...) with my boolean filter condition is the culprit. The measure takes about 15-20 seconds to calculate upon each new selection. The datamodel is neither complex nor large (I'm working with a tiny subset of the real data). Note that the TREATAS comes from the fact that I have a relationship (many:many, if that matters) between my Fact table and a another table ("ASP") where all prices are already calculated per article, company and year-month (it's not connected to the calendar, hence TREATAS). The thinking behing this was simply to avoid summarizing too much on the fly. Data model My questions are: 1) Has anyone ever done this specific kind of analysis; 2) Is there anything you see directly here that should change for a better performance? I will also gladly accept any improvement suggestions. Thanks in advance!
Need help on below scenario
I got data having 2 columns (Product and Age) as below and calculate valid event for each product as shown in "Valid event". I have tried "Earlier" & Ranking function but could not able get to the full result. Please share your thoughts. Product Rank Age Valid event A 1 0 1 Default valid event A 2 9 1 >= 6 gap from first valid event A 3 10 A 4 12 A 5 16 1 >= 6 gap from second valid event A 6 19 A 7 22 1 >= 6 gap from Third valid event A 8 29 1 >= 6 gap from forth valid event A 9 30 A 10 36 1 >= 6 gap from five valid event B 1 0 1 Default valid event B 2 1 B 3 2 B 4 4 B 5 6 1 >= 6 gap from first valid event B 6 8 B 7 25 1 >= 6 gap from second valid eventLatest Invoice Amount - Iternation
Hello, I Have a table of invoices to that was submitted to the health insurance company that looks similar to this: Invoice No. Mode (1- submission, 2- Resubmissition, 3- Correction) Amount VAN10001 1 100 VAN10001 2 100 VAN10002 1 500 VAN10002 3 420 VAN10003 1 800 VAN10003 3 950 VAN10003 2 950 I want to create a column that has the latest invoice Amount where any correction happend, smiliar to below Invoice No./ Amount VAN10001 100 VAN10002 420 VAN10003 950
Calculate values in one fact table from another fact table
Hi , I have following data model which has 2 fact tables 3 dimension tables and 1 bridge table to resolve many to many relationship. I am trying to calculate Sales Volume since the contract Start Date for each "Product ID" in Contracts Table using following formula Sales Volume Since Contract Start Date= CALCULATE ([Total Volume], FILTER ( SalesFact, SalesFact[INVOICE_DATE] >= MIN ( Contracts[Contract Start Date] ) ), SalesFact[PRODUCT_ID] IN VALUES ( Contracts[Product ID] ), SalesFact[BILL_TO_CUST_ID] IN VALUES ( Contracts[Bill To ID] ) ) I get correct results when I throw this measure in a table which provides Context for MIN ( Contracts[Contract Start Date] ) . However, if I throw this measure in a card visual or in any other visual without a Contract Start date context I get incorrect results. Can somone please tell me how can I resolve this? I think I need to use somekind of iterator function to get the result I need, but I can't seem to figure it out. Thanks!
