Forum Discussion

BLAWHEEM1's avatar
BLAWHEEM1
Icon for Helper I rankHelper I
5 years ago
Solved

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 d...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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))

v-zhenbw-msft's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity Support
5 years ago

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]))

 

 

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])

 

 

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.