Measures work for Calendar Year but not Academic Year

[akbar]

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

[akbar]

Hi @v-shex-msft 

Thanks for all your time and help. it seems enough time has passed that the measures can now pick up the neccessary dates and it appears to be fully functional, I appreciate all the help.

Thanks,

Akbar

[v-shex-msft]

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

[akbar]

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)

                                                           

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]

Link to actual file

https://1drv.ms/x/s!AtdW26HVhBpigQQEcWQjNP9d6P-J

Thanks

[v-shex-msft · Accepted solution]

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

[akbar]

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

[v-shex-msft]

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

[akbar]

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

[v-shex-msft]

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

[akbar]

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

[v-shex-msft]

HI @akbar,

Actually, time intelligence function not works properly on on unfinished date range. It only calculate with existed part instead full range.

For this scenario, I think you had to manually calculate the correspond ranges as filter conditions.(date function)

PYT =
VAR seleted =
    ALLSELECTED ( 'calendar' )
VAR _start =
    MINX ( ALLSELECTED ( 'calendar' ), [date] )
VAR _end =
    MAXX ( ALLSELECTED ( 'calendar' ), [date] )
RETURN
    CALCULATE (
        SUM ( table[amount] ),
        FILTER (
            ALL ( 'calendar' ),
            [date]
                >= DATE ( YEAR ( _start ) - 1, MONTH ( _start ), DAY ( _start ) )
                && [date]
                    <= DATE ( YEAR ( _end ) - 1, MONTH ( _end ), DAY ( _end ) )
        )
    )

Regards,

Xiaoxin Sheng