cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Really need some help... Date Table School Terms

Have searched the forums but not found anything that helps me out.  Here is my issue, I need a date table that understands school terms and works with these in a similar way to quarters in a normal date table, is understands which quarter is before/after another...  

 

I have managed to be able to define a custom date table to get weeks numbered correctly, ie week beginning 3rd Sept is week 1,  however it needs to go further...

 

I need to be able to define a date table that understands which term each date belongs to, ie Autumn, Spring and Summer.  Each year the dates will change and there is no standard logic to the number of days per term.

 

TermOrderStart dateEnd date
Autumn103/09/202004/01/2021
Spring205/01/202118/04/2021
Summer319/04/202131/08/2021

 

 I need to be able to do calculations that look at "previous term", in the way that previous year/quarter would work. 

 

For example I have a fact table with attendance marks for each date, so I can count how many present marks for a term and work out the percentage attendance, based on a slicer which has the terms in (have a measure which picks up current selection).  I can then display this in a table visual; however the bit where I struggle is trying to also show the previous terms data based on currently selected term from the slicer in the same visual.

 

Any suggestions would be much appreciated!!!  Thanks in advance 🙂

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@BLAWHEEM1 , Make sure you do it in the Separate term table.

Create a Rank on start Date

New column

Term Rank = RANKX(all('Date'),'Date'[Start Date],,ASC,Dense)

 

measure example

This Term = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Term Rank]=max('Date'[Term Rank])))
Last Term = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Term Rank]=max('Date'[Term Rank])-1))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi @BLAWHEEM1 ,

 

Sorry for that we are not clear about your issue.

Do you want to expand the date between start date and end date?

If yes, please refer the following steps.

 

1. Create a date table.

 

Date table = CALENDAR(MIN('Table'[Start date]),MAX('Table'[End date]))

 

R1.jpg

 

2. Then create a result table.

 

Result Table = 
SUMMARIZE(
    FILTER(
        CROSSJOIN('Date table','Table'),
        'Table'[Start date]<='Date table'[Date] 
        && 'Table'[End date]>='Date table'[Date]
    ),
    'Table'[Term],
    'Table'[Order],
    'Date table'[Date])

 

R2.jpg

R3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

Thanks @v-zhenbw-msft 

 

Probably not been very clear!  I have moved on slightly and managed to create my date table with custom hierarchy, but have slightly different issue with measures.

 

Please see later post below - would be grateful for any insight as I will have to repeat this example with a variety of data showing currently selected and previous term side by side.

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dates-Not-quite-sure-how-to-describe-this-in-...

 

Thanks again!

Super User IV
Super User IV

@BLAWHEEM1 , Make sure you do it in the Separate term table.

Create a Rank on start Date

New column

Term Rank = RANKX(all('Date'),'Date'[Start Date],,ASC,Dense)

 

measure example

This Term = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Term Rank]=max('Date'[Term Rank])))
Last Term = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Term Rank]=max('Date'[Term Rank])-1))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi @amitchandak 

 

Sorry if being bit dense, new to all this and self taught....

 

When you say do it in separate term table, are you saying I should have a table per the post which just shows the term dates, ie this one:

 

TermOrderStart DateEnd Date
Autumn103/09/202004/01/2021
Spring205/01/202118/04/2021
Summer319/04/202131/08/2021

 

Thanks for taking time to reply!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors