cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akbar Frequent Visitor
Frequent Visitor

Measures work for Calendar Year but not Academic Year

Hi,

 

Thanks for anyone that takes the time to read this, apologies it's long. First time posting. I am new to Power BI. I am creating a report which will show the number of orders of a product per quarter. I have date fields (year, quarter) a count of a field (to show the number of orders), a field for Previous Year, YTD, YTD this time last year, Bgt, F'cst. Bgt and F'cst are imported in via CSV files while most others are measures.  The issue I am having is;

 

The report needs to use academic quarters and year, where Sep-Nov30 =Q1    Dec-Feb=Q2    Mar-May31=Q3    Jun-Aug31=Q4.

 

Where Sep-Nov 2017 = Q1-2018,  Dec2017-Feb2018 = Q2-2018,   Mar2018-May2018= Q3-2018,   Jun2018-Aug2018=Q4-2018

 

Term: Sep-Feb (end of)  = H1      Mar-Aug = H2

 

I created a date table which is linked to the main table based on order date, and defined the academic date fields using DAX;

 

My date table is called calendar (only realized late on that I spelt it wrong ha)

 

Term = IF(Calandar[Month Number]>2 && Calandar[Month Number]<9, "H2", "H1")

 

Academic Quarter = IF(Calandar[Month Number]>=9 && Calandar[Month Number]<=11, "Q1", IF(Calandar[Month Number]>=12 || Calandar[Month Number]<=2, "Q2", IF(Calandar[Month Number]>=3 && Calandar[Month Number]<=5, "Q3", "Q4")))

 

Academic Year = if(Calandar[Month Number]>8, Calandar[Year]+1, Calandar[Year])

 

Academic Qtr Yr= Calandar[Academic Quarter] & "-" & Calandar[Academic Year]

 

Problem I have is that my measures only work based on the calandar year not for the defined Academic Year.

 

Is there any way around this?

 

I also cannot get YTD This Time Last Year working (regardless of calendar date or academic date).

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Measures work for Calendar Year but not Academic Year

Hi @akbar,

 

The simple way is add academic year columns to calendar table as reference.
You can take a look at below link to know more about how to deal with fiscal year or other similar unreal date range.

DAX Formulas for Fiscal Year, Fiscal Quarter and Month Short Name

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

11 REPLIES 11
Community Support Team
Community Support Team

Re: Measures work for Calendar Year but not Academic Year

HI @akbar,

 

Can you please share some sample data or pbix file for further test? I think it will be help for checking formula.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
akbar Frequent Visitor
Frequent Visitor

Re: Measures work for Calendar Year but not Academic Year

Hi @v-shex-msft 

 

Thank you for the response. I have mocked up some data in excel for how it looks.

 

Main Table (data comes from a data mart)

 

 main table.JPGdisplay.JPG                                                          

The colour key is just a reference for you know what the fields are, they are of no importance to the actual report or layout

 

The orders for future dates will increase as the orders can be made a year in advance (this data pulls through from a data mart)

 

I also have a date table which I created. It has the usual fields (date, month, year, quarter, month number etc) plus the specific academic fields that I created (dax code for those are in the orginal post). 

 

There are also other csv files imported but those are merely for further breakdowns into categories. They arent used in any formulae so I dont think they need to be mentioned.

 

I can post the code for the measures if you would like, most of which were found on this forum. The 4 variance measures are simply one measure divide by the other minus 1

 

Thanks,

 

Akbar

akbar Frequent Visitor
Frequent Visitor

Re: Measures work for Calendar Year but not Academic Year

Community Support Team
Community Support Team

Re: Measures work for Calendar Year but not Academic Year

Hi @akbar,

 

The simple way is add academic year columns to calendar table as reference.
You can take a look at below link to know more about how to deal with fiscal year or other similar unreal date range.

DAX Formulas for Fiscal Year, Fiscal Quarter and Month Short Name

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

akbar Frequent Visitor
Frequent Visitor

Re: Measures work for Calendar Year but not Academic Year

hi @v-shex-msft 

 

Thank you for your response, this has sorted out the YTD in the academic format. However I still cannot get Previous year or YTD Previous year to work for the academic year. I already have academic year, academic quarter in my calendar table. as stated before, PY workas for calendar year but not academic year. I have tried watching videos to see for a solution but alas no luck, any ideas?

 

Thanks,

 

Akbar

Community Support Team
Community Support Team

Re: Measures work for Calendar Year but not Academic Year

HI @akbar,

 

In my opinion, academic date should same as fiscal date, you can use same function to deal with them.

 

Sample:

1. Use allselected function to get summary academic date range, crate a variable to store corresponded normal calendar range.

2. Use time intelligence functions to operate with above date range or manually calculate out previous part of variable date range.

3. Use converted date range in filter expression.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
akbar Frequent Visitor
Frequent Visitor

Re: Measures work for Calendar Year but not Academic Year

Hi @v-shex-msft 

 

Thank you.

 

I appear to have the measures working now. when showing the measures alongside the dates they work. However when I try to display the measures alongside the categories it wont display, which I assume is due to their being no relationship between fact table and the measures. I thought maybe the way around would be to build the measures into calculated columns into the fact table, however it doesnt seem possible to have the same measures as calculated columns. Is this the case or am I just doing something wrong?

 

Thanks,

 

Akbar

Community Support Team
Community Support Team

Re: Measures work for Calendar Year but not Academic Year

HI @akbar,

 

>>which I assume is due to their being no relationship between fact table and the measures.

If your tables not contains relationships and you also remove that category column, it may caused the issue.

 

In my opinion, I'd like to suggest you can add allselected(table[Category]) or values(table[Category]) as filter condition to your original formula to help calculate.(these function can get data from slicer of other filters even if your tables not contains relationships)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
akbar Frequent Visitor
Frequent Visitor

Re: Measures work for Calendar Year but not Academic Year

Hi @v-shex-msft 

 

Thank you, it may be something I need to do at some point. But thankfully I have the measures somewhat working now. It seemed that the issue was to do with the cross functionality of the relationships. The date table to fact table (table from data mart) had to be single which sorted the measures while the fact table to csv files had to be both.

 

Apologies but I have one last question/problem.

 

I have an issue with my measures for the Q1-2019 values (which would represent the date range 1st september 2018 - 30th November 2018). While PY and YTD PY work perfectly for comparing 2017 and 2018 academic years, Q1-2019 seems to give random values. E.g. for Q1-2019, PY should equal Q1-2018, but it doesnt (works perfectly for Q1-Q4 of 2018, bringing back Q1-Q4 2017 respectively). It should be worth noting that for most categories and such there are no values for Q1-2019 yet, some do have a small amount of records however. Could this be effecting it?

 

Are you aware of what could be the issue?

 

Apologies for all the questions, I am very appreciative of all the help.

 

Thanks,

 

Akbar

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)