DAX Measure that accounts for previous dates data and not future dates data based on current date
Hello everyone, I am working on a financial report that is connected to an excel document full of referenced data. In Power BI desktop, I am hoping to display the average COGS per day (Cost of Goods Sold) in the form of a PBIX card on a Year to Date scale. Chronologically speaking, the data is organized by week (Mon-Sun). I have references set up in excel to capture future data as it occurs for this year. Below is an example mockup to better visualize my data/request: The issue I am having here is that the average COGS/day is taking into account future weeks where the data is evidently zero. For instance, today's date is 7/21, so all future weeks (weeks of 7/25, 8/1, etc.) are being included in this average and are therefore skewing this metric. I am hoping to write a measure that only accounts for weeks prior to the current date. Any help/tips on my ask would be very appreciated! Thank you in advance.
Simple Cost Ratio from GL Data
Hello all, pretty frustrated with what I feel should be a simple task I have a simple set of GL data, as seen below, where my amount is in one column, and my accounts are rows. I then do a very simple model where I have a GL Account class lookup table, which assigns the class "revenue" and "cost" to each account. I then just want to have a simple cost ratio at the account level, of cost/total revenue. the table should look as such below, but nothing returns at the account level for a margin. even the revenue accounts are wrong, as Revenue Reg should be 60% of total revenue, , and Revenue Spec should be 40% My measures are Value = SUM(data_table[Amount]) Total Revenue = CALCULATE([Value],gl_class[Class]="Revenue") Cost Margin = DIVIDE([Value],[Total Revenue]) any help would be greatly appreciated
DAX formula for financial report
Hi all, i'm working on a financial report in PowerBI and i already had a lot of inspiration from the internet, but there is one problem now and I really don't know how to sort this. I have caclulated some subtotals, for "omzet", 'Brutowinst" and 'Operationele kosten'. this all works. Then for all other costs, i have made a calculation called 'Actuals'. This also works fine. Now i have an Financial template with RowID to make sure the table will be in the correct sorting order and to make sure the row names will look properly with some spaces before descriptions. So this is my model: I'm told that you should not link the financial template to the rest of the tables, as it is only used for rownames and the values will have to be calculated and not linked. Then, i have made a DAX formules that should decide in the financial template table (on the right) if there is a subtotal calculated or not. If yes, it should return the subtotal amount (this works fine), if no, it should give the 'actual' amount (as in the table on the left). But as you can see, it returns the total of the acuals almost everywhere, and not the individuals actuals for each row. But what i want, is a subtotal and otherwise the acual amount fot that specific row. These are the formulas I have used now: for the actuals: for the Selected Years Actuals: Probably I made a mistake in the CALCULATE part at the end? But I don't know how to fix it, so i hope somebody can help me with this? It would be much appreciated! Kind regards, Marieke
