topic Re: Multiple Dates in Desktop

Multiple Dates

M_SBS_6 — Thu, 11 Apr 2024 15:14:20 GMT

Hi, I have 2 tables joined to each other apps table to comps table (one to many).

***I need these 2 tables to join to show the other visuals within the dashboard***

I have 2 dates that are needed apps[app_date] and comps[comp_date] as I want in my table to show how many apps and comps we've had side by side each year.

The problem that I have is that I want a year column. If I select year from app_date hierarchy or comp_date hierarchy, it skews the numbers.

This is what I want to see:

Calendar[date] Apps_vol. Comps_vol

2022. 10. 9

2023. 15. 5

2024. . 4. . 1

If I use the app_date, my apps_vol is as above but my comps_vol changes.

I created a calendar table and joined apps[app_date] to calendar[date] and this is active.

I then joined comps[comp_date] to calendar[date] and this join is inactive (probably due to joining apps to comps].

Any idea what I could do to get around this please?

Re: Multiple Dates

johnbasha33 — Thu, 11 Apr 2024 15:04:05 GMT

M_SBS_6

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 !!

Re: Multiple Dates

M_SBS_6 — Thu, 11 Apr 2024 15:15:41 GMT

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.

Re: Multiple Dates

MOVC — Thu, 11 Apr 2024 15:54:44 GMT

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