Variance between Two Years on same Date Axis using same Amount Column
Unfiltered: Filtered: The task at hand is simple, take the 2025 values (Food Revenue, Beverage Revenue, etc.) and subtract the 2024 values from the correlated field (first screenshot). However, I am running into issues with the date axis it lies on and how to correctly display only the months needed. The formula used takes the sum of the current year (CY, Dec 2024 - Mar 2025) for a specific account, and subtracts it from the balance of the prior year (LY, Dec 2023 - Mar 2024). This does in fact work, as the values shown in the second screenshot are correct for Food Revenue Variance for the months needed thus far (fiscal year starts in December, so December 2024 - March 2025). However, when I filter to just show the months needed, the values revert to only show the balances correlated with the current year (CY). Any ideas on how to show this variance correctly? In layman's terms, basically need to INCLUDE data from all months relevant (Dec 2023 - Mar 2024, Dec 2024 - Mar 2025), but I only need to DISPLAY the months with the correct values for variance (Dec 2024 - Mar 2025). DAX measure: VARI Food Revenue = VAR CY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = 0) VAR LY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = -1, REMOVEFILTERS(DateDim[Date].[Year])) RETURN CY - LYRead slicer values, retrieve corresponding table column values and use them to check existence
Hi all, I am trying to retrieve a column values based on the selected single/multiple slicer values and use those data to check their existence in the other table. Let me put this out clearly. There are two tables i.e., Calendar and Employee data. The calendar has the following columns i.e., fiscal year, fiscal year period, date. Adding the screenshot below. Calendar table: The Employee table has Name, Location, Joined Date. Employee table: The slicer in the Power BI dashboard is added with the 'Fiscal year period' column from 'Calendar' table. If the user selects a particular fiscal period or multiple fiscal periods, the corresponding dates from the Calendar table have to be read in a dax calculation and check the existence of those date values in the Joined date column in the table 'Employee'. For instance, if the user selects 2025 P3 and 2025 P4 from the slicer, then the corresponding dates from 3rd March to 4th May have to be read and check if any of those values exist in the column from other table i.e., in Joined date from Employee table. Could you please help me with a dax calculation/measures for this scenario? Any suggestions will be appreciated. Thanks! Microsoft EnterpriseDNA
Display Two Different Dates from the same point on the X axis.
Hi, me again. I want to plot two lines that start from different dates in POWER BI. I have products that have a differents date of creation and i have to display the product since it launch on a line chart as X=0, so that my users get an understanding of how the respective product have fallen in delincuency product launch. This is what i currently have: This just a running total of the percentage of clients that fell into delincuency, but notice that both of these linnes start at different dates, they should star at point 0, measured by the numbers of weeks since the product launched. Sometihng like: Imagine that in the X axis we have numbers from 0 to whatever and that both start from 0, i just picked the two closest that i had. I'll leave a mockup file attached, product of previous help: https://drive.google.com/file/d/1uems6jCzxqkJHNdtcnXxxHxBo222XPB1/view?usp=drive_link
Sum for most recent date available
I have a table that records sales by date. I need to be able to display the total sales for each date and also display the prior date's total. What is a measure in dax that I can use to sum based on whatever the next most recent date is? I tried doing a RANKX column on the record dates, which works correctly in providing a numbered rankings for date, then used the below: Prior Date Sales = CALCULATE(SUM(RecordDate[Sales], RecordDate[RankNo] = RecordDate[RankNo]-1) However when I use this measure in a table with the date and total sales, the Prior Date Sales is blank. Is there anything I'm missing with this measure? Sample of data and the result I'm trying to accomplish: Table: Record Date Sales 5/13/2024 100 5/9/2024 200 5/9/2024 200 5/2/2024 300 5/2/2024 300 4/28/2024 400 4/28/2024 100 Totals: Record Date Total Sales Prior Date Sales 5/13/2024 100 400 5/9/2024 400 600 5/2/2024 600 500 4/28/2024 500
Find the max date that is less than a specific date, considering a grouping factor (Power Query)
Hi, I have the following table in Power Query: I need to add a column that follows the following logic: if Date1 = Date3 then Date1, otherwise find the maximum date in Date3 that is less than the Date1 of each row, considering the group ID. Is it possible to do this in Power Query? ThanksPrevious Month Calculation to Forecast
Hi all, I have the below data: Month Headcount Hires Other Increase Other Decrease February 400 0 0 March 20 10 20 April 5 10 0 May 10 0 0 Problem statement: I need to create a calculation that sums up the headcount, hires, increases and decreases for future months. I want March to take February's headcount and add March's respective Hires, Other Increases and minus Other Decreases. For example March would be 400 + 20 +10 -20 = 410 April would then take March's value (410) and perform the same calculation: 410 +5 +10 - 0 = 425 May would then take Aprils number and so on... I am having trouble trying to get each month to take the calculated vale of the month before. Please help and thank you in advance.
