Forum Discussion
Multiple Dates
You could try a new table based off the two existing ones instead of using a calendar table.
Take a copy of the Apps and Comps table and turn them into single date field and make sure they're named the same (just "Date"), then for the Apps one add a custom column called "Apps" that is just equal to 1 then do the same for Comp called "Comp". You can then append these ontop of each other to get a three column table. Extract the year from the date and then group on the year using Sum on the other columns
To achieve your desired outcome where the counts of apps and comps are side by side for each year, you can follow these steps:
1. **Create a Calendar Table**: Create a calendar table that includes a date column (`Date`) covering the range of your data.
2. **Create Relationships**: Create relationships between your calendar table and both the apps and comps tables based on their respective date columns (`app_date` and `comp_date`).
3. **Create Measures**: Create measures to count the number of apps and comps for each year. These measures should use the `CALCULATE` function along with `FILTER` to filter the data based on the selected year.
```DAX
Apps_vol = CALCULATE(COUNTROWS(apps), FILTER(apps, YEAR(apps[app_date]) = SELECTEDVALUE(Calendar[Year])))
```
```DAX
Comps_vol = CALCULATE(COUNTROWS(comps), FILTER(comps, YEAR(comps[comp_date]) = SELECTEDVALUE(Calendar[Year])))
```
4. **Create a Matrix Visual**: Create a matrix visual with the calendar year (`Year`) on rows and your measures (`Apps_vol` and `Comps_vol`) on columns.
- Put the `Year` column from your calendar table on the rows of the matrix.
- Put the `Apps_vol` measure on the columns of the matrix.
- Put the `Comps_vol` measure next to `Apps_vol` in the columns of the matrix.
This setup should give you the counts of apps and comps side by side for each year, without skewing the numbers when selecting a year from the hierarchy. The relationships between the calendar table and both the apps and comps tables ensure that the counts are calculated correctly based on the selected year.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Thanks for your suggestion but the problem I have is that when i remove the join from apps to comps, my other dashboard calculations and visuals break.