User Profile
Julius410
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Re: CURRENT WEEK & FUTURE WEEKS CALCULATION
Thank you very much for your detailed answer. The calcuation for the current week worked. However, the future balances and weeks cover give me the following error "The Column... cannot be pushed to the remote data source and cannot be used in this scenario." I am using a table visual now and calculate the columns seperately which works as well. Thanks again.CURRENT WEEK & FUTURE WEEKS CALCULATION
Hi there, I am looking for a solution to calculate inventory projections. I have a matrix that contains the following colums for the current week (ending Sunday). Columns here is Week Ending. For example, 17/12/2023 and values are: Item Code Stock On Hand Production Demand Balance Weeks Cover Specifies the code of the item Amount of stock that is currently on hand Amount of stock that should be produced until the end of this week Amount of stock that customers requested until the end of this week (Stock On Hand + Production) - Demand (Stock On Hand + Production) / Demand I have another matrix that contains th columns for the next week (e.g., 24/12/2023) and up to 12 weeks in the future. In this matrix, I would like to calculate my inventory projections and it should look like this: Item Code Stock On Hand Production Demand Balance Weeks Cover Specifies the code of the item Balance of Previous Week Amount of stock that should be produced until the end of this week Amount of stock that customers requested until the end of this week (Stock On Hand + Production) - Demand (Stock On Hand + Production) / Demand I am looking for a calculation that allows me to specify the current week and the previous week. So far, I have used this calculation: week number = WEEKNUM(Table[Date]) current week number = IF(WEEKNUM(Table[Date])=WEEKNUM(TODAY()),1,0) This formula does not work correctly because for the current ending week (17/12/2023) it gives me two values: 1 and 0. Would anyone know how to solve this? Many thanks. JuliusRe: COUNT MONTHS for FY
Hi, I can't upload the file here or create a link to download it. Essentially, the file can contain two tables: 1) Date Table and 2) Data Table. For the date table you can use this code - starting month is 7: let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table => let DayCount = Duration.Days(Duration.From(EndDate - StartDate)), Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text), InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])), InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])), InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text), InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])), InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]), InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text), InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])), InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])), InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])), InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date), InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])), InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100), InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100), ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}), InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text), AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear])) in AddFY in fnDateTable The data table only needs two columns: Date Data 01/06/2021 420 01/06/2022 371 01/07/2023 46 And then connect the date table with the data table via the date column. Does that work for you? Thank you. JuliusCOUNT MONTHS for FY
Hi there, I am trying to calculate a measure that allows me to divide an achieved result for a financial year by the number of months that have passed in the financial year. For example, 420 were achieved in FY22. This means for FY22 I want 420 / 12 = 35. For FY24, 2 months should be considered (our FY starts in July and we're in August) and should show 46 / 2 = 23. Please see graph below: How could this be achieved? Thank you. JuliusRe: IF Formula Calculated Column
Hi Anonymous Thanks so much for your extensive reply and the detailed explantion. Your solution is getting close. However, it's still not exactly what I wanted. The problem is that the number initiatives stay the same regardless of which filter is selected. Please see below the screenshot for slicer "Budgeted": Please see below screenshot for slicer "Budget N/A": I would like the initiatives that do not have a budgeted value against them filtered out when I select "Budget N/A". Do you think that this is possible? Thanks. JuliusIF Formula Calculated Column
Hi there, I am trying to create a calculated column that allows me to differentiate between projects that have a budget and projects with no budget. Currently, I am using the following formula: Budgeted = IF( 'Impacts Final'[Budget] = 0, "Budget N/A", "Budgeted") Unfortunately, Power BI does not display the desired outcome. It displays the correct classification of budgeted and non-budgeted projects. It does not display forecasted or actual values for the "Budgeted" projects. However, it does display actual and forecasted values for projects with "Budget N/A". Please see screenshot below: Can someone help me to solve this issue? Thank you. JuliusRe: Time Specific Events
Hi amitchandak, Thanks so much for your quick response. Unfortunately, your code does not present the desired results. Here is what I entered: Measure 1 = CALCULATE(COUNTROWS(Table1),FILTER(Table1, Table1[Time] >= NOW() - TIME(6,0,0) && Table1[Machine] = "Machine 2")) Top Events = CALCULATE([Measure 1], TOPN(2, ALLSELECTED(Table1[Event]), [Measure 1], DESC), VALUES(Table1[Event])) When I put the the "Top Events" measure in a table together with the column "Event", I get the error code: "Can't display the visual." I tried to understand your code bit by bit and I am not sure if it would display what I am trying to figure out. Just to clarify, I am trying to find the most popular events in Machine 1 & 3 that appeared 6 minutes before the stop event in Machine 2. Please see below a link to the mock dataset: https://www.dropbox.com/scl/fi/xeghphf7de0ikn39gdgk9/Mock-Data.xlsx?dl=0&rlkey=y3wp99mwb1yddube2i9b4sq3o Please see below a link to my current Power BI working file: https://www.dropbox.com/s/cklczwscixo63la/Test_28022022.pbix?dl=0 Again, thank you for your help. Julius
