I'm new to Power BI and DAX.
I want to show the total ID count of this year and the total ID counts of previous years at the same dates each of which is associated with a different Category_Number.
The below tables show the original data set (1st table) and the result table (2nd table) I would like to have.
Any ideas or suggestions will be greatly appreciated.
Thanks!
You want to filter and use SAMEPERIODLASTYEAR.
To get the values of one category you can do:
Category_Nbr 80 = CALCULATE(COUNTROWS(myTable),myTable[Category_Nbr] = 80)
for the value of this but for the previous year you could use your DATE TABLE if you have it, if not, use the automatic one. I'll assume you don't have one so use the automatic.
Category_Nbr 80 LY = CALCULATE([Category_Nbr 80],SAMEPERIODLASTYEAR(myTable[Effective_Date].Date))
With this you can do a comparison over time
Category_Nbr Count YoY = DIVIDE([Category_Nbr 80 LY]-[Category_Nbr 80],[Category_Nbr 80 LY])
You can just adapt the filter for other categories.
Consider having a look into Quick Measures really nice way to start learning DAX.
Below are two measures I created.
The Total_ID measure works well. It shows the total number of IDs that have the Category_Number =80 and Effecitve_Date < Today.
The Total_ID_LY measure shows the total number IDs that have the Category_Number = 70, but doesn't show the Effective_Date< The same data last year.
I want the Total_ID_LY measure to have two filters, Category_Number = 70 (80-10) and Effective_Date < The same data last year of today.
Any helps? Thanks!
Total_ID = COUNT('myTable'[ID])
Total_ID_LY = VAR CurrentCategory = SELECTEDVALUE ( 'myTable'[Category_Number] ) VAR PreviousCategory = CALCULATE ( MAX ( 'myTable'[Category_Number] ), ALLSELECTED ( 'myTable' ), KEEPFILTERS ( 'myTable'[Category_Number] < CurrentCategory ) ) VAR Result = CALCULATE ( [Total_ID], 'myTable'[Category_Number] = PreviousCategory ) RETURN Result
