Filter by dates on dates as columns

[Syndicate_Admin]

Hello community

I'm starting out on Power BI and I ran into the following problem:

In my current job I was asked to filter by date, which workers were during the different days of the month, I have the fields: date of entry, ID (not very important, they are used to obtain the total of workers), name and numbers from 1 to 28, 30 or 31 according to the corresponding month, the numbers would be the days of the month and are filled down with information such as T of work or D of rest according to what X worker did that day, I will put an example image so that the idea can be better understood

Is there a way to do what I need to do?, can you create a calendar table and link the numbers and somehow interpret them as dates? or do you have to enter to modify the structure of the table? I stay tuned for any kind of information or suggestion about it, in advance thank you very much

[Syndicate_Admin]

Hello! thank you very much for the time to reply and help me get closer to what I need to solve, well, the problem itself was solved, now I can link my calendar table with the dates I had before in columns, but, I can't use the filters in the columns to be able to display information,

the calendar table was generated with date from 31-12-2021 and only on that day the filter shows something, but if I move to 01-01-2022 it does not show information even the tables having information on those dates, I tried to solve this problem with quick measures and normal measures but so far I can not solve this problem, I will give an example of what I am trying at the moment

measure to count how many workers there are in total

TOTAL =

VAR Validate= DISTINCTCOUNT('JANUARY 2022'[NAMES]) //previously used count but as the pivot was removed the records were sorted differently

RETURN

IF ( ISBLANK (Validate),0, Validate) // change if blank instead show zero and not blank

measure to count how many workers attended their work

OPERATIONAL =

VAR Validate=

CALCULATE(

DISTINCTCOUNT( 'JANUARY 2022'[NAME] ), //count different names

RIGHT( 'JANUARY 2022'[NAME], 3 ) = "T", //count in January 2022 (that's the name of the excel sheet with the info.) records of up to 3 characters containing the letter "T" of worked

FILTER ( 'CURRENT CALENDAR', 'CURRENT CALENDAR'[DAY] )) //I apply filter per day

RETURN

IF( ISBLANK ( Validate),0 , Validate) //change "blank" to zero

Thank you very much in advance

[v-janeyg-msft]

Hi, @Syndicate_Admin 

It's strange. Did you establish a relationship between the dates? If your calendar table is created without problems, it should be able to filter normally. Can you show me a screenshot of the data model?

[Syndicate_Admin]

Hello again! And thank you very much, as you asked me, I attach an image of how I have the relationship of the tables made to each other, they are related by the date field (Current Calendar / Dates | JANUARY 2022/Date of the Month)

 

I also noticed that you translated my reply, so if spanish is difficult for you to understand or have to spend time translating it, feel free to reply to me in english if it is better for you, i have no problem on both languages, since i am native spanish speaker and have very decent english level

Gracias / Thank you

[v-janeyg-msft · Accepted solution]

Hi,  @Syndicate_Admin 

I really don't understand Spanish, this is translated by the system, I suspect there is something wrong with the translation, because I really didn't find your problem.

How your calendar table was created? Is the data continuous?

Is it like this: 

datetable = CALENDAR ( DATE ( 2021, 12, 31 ), DATE ( 2022, 12, 31 ) )

[v-janeyg-msft]

Hi, @Syndicate_Admin 

Accoding to your description, I think you need to change the model in power query.

Like this:

First, unpivot your date columns.

Change the new column type, Then create a custom column to display the corresponding date.

#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),[Attribute])

Result:

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey