reset data on a due date

[Anonymous]

Hello,

I'm still new to Power BI and I feel like I'm in a dead end.

I want an overview on how the workers work every month, but the thing is that they work kind of "between" months...

For example, for August, I want to see their work between August 15th and September 14th, and I need my data (how many hours they spent, how many files they filled...) to reset every 14th so I can follow their work and deadlines and all.

Edit :

Workers fill a Forms every time they completed a file. It gives me the data I need + the date they completed it.

I hope it's clear,

Thank you

[TomMartens]

Hey,

basically it's quite easy if you create a separate calendar table, just by using an Excel Sheet.

Create a column called date, add all the dates that you want to analyze and make sure you are using a real date datatype.

Create a second column called "Special Months", put the appropriate values to this column. Considering that your calendar will span multiple years, so August, may become August 2019. If you can create an Excel formula that creates the propoer month values it will be also possible to create all this by using DAX statemens.

Import the calendar table to your pbix file, relate the Date column from the calendar table (on the one side) to the date column of the table that contains the data of the workers, use the column "Special Months" in your reports - done!

Hopefully this provides you with some ideas.

Regards,

Tom

[Anonymous · Accepted solution]

Hello,

I did it, thanks for your help, actually it was quite simple but i didn't think of it in the first place !

It goes like this :

Date = ADDCOLUMNS (

CALENDAR (DATE(2019;1;15); DATE(2029;1;14));

"DateAsInteger"; FORMAT ( [Date]; "DDMMYYYY" );

"Année"; YEAR ( [Date]-DAY(15) );

"Mois"; FORMAT ( [Date]; "MM" );

"Mois et année"; FORMAT ( [Date]; "MM/YYYY" );

"Période sociale"; FORMAT ( [Date]-DAY(15); "mmmm" );

"Jour de la semaine"; WEEKDAY ( [Date]-1 );

"Trimestre"; "Q" & FORMAT ( [Date]-DAY(15); "Q" );

"Trimestre et année"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ))

Basically I set some "-DAY(15)" on values I wanted to shortened and I also wanted my weeks to consider monday as the first day of the week, that's why there's a "[DATE]-1"

Sorry for the french words in my formula ahah

[Anonymous]

Thank you for the reply, I have built my table, and will see tomorrow if it works !

I will edit this comment in order to keep you aware of my situation.

Thanks again !