Year-Over-Year calculation
Hi Everyone, I am completely new to Power Pivot and DAX calculations. I have a simple data model with a date table and a fact table with dates and sales generated per date. Each month, I am generating a report that shows MTD and YTD sales vs Last year for the same period. Is there a DAX formula to show this.DAX - Last year customer retention
I have a measure that calculates customer retention: customers who bought in the last 3 months. I need to bring the comparison of this measure from the previous year. I tried the formula below without success. customer retention: customers who bought in the last 3 months. Frequencia pedidos Trimestral = VAR AllOldCustomers = CALCULATETABLE ( VALUES ( fVendas[Nome do PN] ), fVendas, fVendas[Data NF] < TODAY () - 90, fVendas[Documento] = "Nota fiscal de saída" ) VAR AllNewCustomers = CALCULATETABLE ( VALUES ( fVendas[Nome do PN] ), fVendas[Data NF] < TODAY () , fVendas[Documento] = "Nota fiscal de saída" ) RETURN COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) comparison of this measure from the previous year: Frequencia pedidos Trimestral PY = VAR PreviousYearDate = DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) VAR AllOldCustomers = CALCULATETABLE ( VALUES ( fVendas[Nome do PN] ), fVendas, fVendas[Data NF] < PreviousYearDate - 90, fVendas[Documento] = "Nota fiscal de saída" ) VAR AllNewCustomers = CALCULATETABLE ( VALUES ( fVendas[Nome do PN] ), fVendas[Data NF] < PreviousYearDate , fVendas[Documento] = "Nota fiscal de saída" ) RETURN IF ( ISBLANK ( ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) ) ), 0, COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) )
DAX year comparison - Why does this not work?
Hi. I'm trying to set up a number of standard formula to be used in my PBI reports. users are supposed to select 1 year. Also they can select additional filters such as YTD and whatever you have . In any case : Asume selected is (only) 2022 in the date filter. (The following works) TEST_TY = VAR __YEARSELECTION = CALCULATE(MAX('Date'[Year])-0) VAR __BASEFORMULA = CALCULATE([Product SO],FILTER('Date','Date'[Year]=__YEARSELECTION)) RETURN __BASEFORMULA (This does not - Except then selecting both 2022 AND 2021) TEST_TY = VAR __YEARSELECTION = CALCULATE(MAX('Date'[Year])-1) VAR __BASEFORMULA = CALCULATE([Product SO],FILTER('Date','Date'[Year]=__YEARSELECTION)) RETURN __BASEFORMULA Why does the following above not work? How do I override the 2022 selection? TEST = CALCULATE(sum(_actuals[product_sales_eur]),FILTER('Date','Date'[Year]=2021)) also does not work Please not that I do not only want to look 1 year back, but will want to do 2020 aswell etc. Thanks!
Calculate the LY y YoY% of headcount
Hello friends, I am working on an HR Analytics dashboard, in which I want to calculate the following with DAX formulas: 1. Calculate Last Year 2. Calculate the YoY% As data to take into account: In the dashboard I will make filters at the year level I already have a headcount DAX : Headcount= SUM('01_Master Headcount'[Headcount]) and the graph that I want to create is exactly this: The data is assembled as follows (you can download the complete excel file with the data from this link: here) I hope you can help me. I'll be very greatful, Nestor ReyesLast Year (LY) Measure Limited by Current Date
Hello All: Would someone help me to build a measure [Sales 2018] that shows last year amount for a selected period? In the example attached to this post, [Sales 2018] for June should be $34.7 M (June 1 - 25), not $42.6 M (sum through June 30, 2018). If I select, for example, May and June [Sales 2018] should be the amount from May 1 through June 25, 2018. And so on. The solutions I've seen so far focus on YTD calculations and are quite confusing. I am trying to build a measure with dynamically changing date rage based on the selection. Thank you all in advance. Alex
