topic Days in month become many in DAX Commands and Tips

Days in month become many

KW123 — Thu, 04 Aug 2022 16:08:17 GMT

Hi,

I have a table report which has two measures.
What does YTD Need to be
Where we actually are YTD

When I have just those two in there, the days of the month are normal. It should go from 1-end of month, no duplicates. The moment I add in my column uploaded from excel (Accounting goal) the days of the week become many for each day. Is there a way to fix this? I need all three in the report. It might also be worth noting that in the table in which the accounting goal lives in has 12 rows, one for each month. In the report, when I add in the Accounting goal with the Where we actually are YTD, it doesn't give me the value for just the sliced month. It goes down the column for each goal of the month which is wrong. If I take out the Where we actually are YTD, it only shows the value for that month which is correct. So it seems that the two columns are not compatable.

Thank you

Re: Days in month become many

littlemojopuppy — Fri, 05 Aug 2022 00:37:58 GMT

Hi KW123 let's see the data model!

Re: Days in month become many

KW123 — Fri, 05 Aug 2022 16:03:07 GMT

littlemojopuppy

I am not sure that I can get that to you without taking the time to block off all the private information. I am not sure if this helps, but the two measure and the column are all in the same table, '2022 Goals' and connected to my dates table by the Month name.

Re: Days in month become many

KW123 — Fri, 05 Aug 2022 16:05:15 GMT

littlemojopuppy

Re: Days in month become many

KW123 — Mon, 08 Aug 2022 01:49:35 GMT

Bumping this up as I still need help with this. Any ideas are truly appreciated!

Re: Days in month become many

littlemojopuppy — Mon, 08 Aug 2022 12:47:34 GMT

Hi KW123 still hard to tell. I'm thinking there's a problem with the relationship between you date table and accounting goals. I would have an actual date in the goals (first day of month, last day of month, anything really...) and base the relationship on that date.

Re: Days in month become many

KW123 — Mon, 08 Aug 2022 17:19:02 GMT

littlemojopuppy Thank you!

How would I add the days of the month with just a Month column? I have tried creating a custom column,
=Date.AddDays([Date],1) and it yields an error.

Thanks!

Re: Days in month become many

littlemojopuppy — Mon, 08 Aug 2022 22:04:51 GMT

Hi KW123

To add a column for the day of month the DAX would be DAY(DateTable[Date]) (adjust the table/field name appropriately)

Re: Days in month become many

littlemojopuppy — Mon, 08 Aug 2022 22:07:00 GMT

KW123 I think you're onto something. Can you share a screen shot of your whole date table?

Re: Days in month become many

KW123 — Mon, 08 Aug 2022 22:29:42 GMT

littlemojopuppy
Here you go. Also I tried adding in Days to the Goals table, but it didn't work. Is it because I am adding it to the goals table? My dates table already has days. The two tables share a relationship with the Month Name.

Re: Days in month become many

littlemojopuppy — Tue, 09 Aug 2022 13:54:39 GMT

KW123 Yep. That's what I thought was wrong. The relationship between the date table and any other table should be on date values. In the goals table, add a field for the first/last day of the month (either will do). Change the relationship using date fields and then you should be good.

By the way...apologies for taking so long to reply over the weekend and last night.

Re: Days in month become many

KW123 — Tue, 09 Aug 2022 18:41:15 GMT

littlemojopuppy
Thanks so much for your help I really appreciate it. No need to apologize!

So I created two columns in the goals table, one for start of month and one of end of month. I fixed the relationship between the dates table and it's still producing the same result where the days are all broken down.... I have double checked Data type too and that looks fine

Re: Days in month become many

KW123 — Tue, 09 Aug 2022 19:18:39 GMT

littlemojopuppy I figured it out!!! My DAX for my YTD column had a +0. Once I took that off it resolved it completley. Thank you so much for your help I really appreciate it

Re: Days in month become many

littlemojopuppy — Tue, 09 Aug 2022 19:48:44 GMT

KW123 de nada. I really didn't do much 🙂