Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

reset data on a due date

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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
Not applicable

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 !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.