How to divide the sales amount into the correct fiscal year?

[Aixia]

Hi

I´m new here and also new with PBI. Nice to e-meet you all .

I´ve problem to accrual sales with PBI.

In my case,

1. FY is September - August. I already have created the Fiscal Calener Date for that;

2. There are 3 dates in my FactSales tabel:

    invoice date ( the day the invoices was issued)

    start date and end date ( invoices refer to the recurring support fee for different months: 1 month, 3 months, 6 months or 12 months, that´s why the start date och end date exist. )

3. I´ve connected the FactSales tabel with with Fiscal Calender Date tabel. 

4. I´ve also created the 2 measures for Total sales:

   Total sales by invoing date 

   Total sales by Start date (using funtion  USERELATIONSHIP)

Comparing these two measures, none of them are correct. 

Sales amount for 2021-23 shows 2 478 112:-.

Actually this amount should divided by two parts: 1/12 part belong to the FY 2021-22 and 11/12 to FY 2022-23. 

My questions:

1. What is wrong with the measure ´´Sales by start date´´? Maybe because of not incluing End date? 

2. How can I creat new measures which take into account the right amount to right FY? 

3. Or do I need to Transform data FactSales table in some speciel way? 

2. Or do I add some new columns on FactSales? If so, what kind of?

Thanks in advance for your help.

Regards

Aixia

[sayaliredij · Accepted solution]

Hi @Aixia 

Yes I understood the problem.

Please change the formule in following way 

Make measure - 

Total Sales SEK (by Start Date ) (hidden) as follows

Total Sales SEK (by Start Date ) (hidden) =

var NoOfDaysInInvoice = calculate(DISTINCTCOUNT('DimFiscal Calender'[Fiscal YearMonth adjusted]), FILTER('DimFiscal Calender',AND('DimFiscal Calender'[Date]>= MIN(FactSales[Start Date ]),'DimFiscal Calender'[Date] <= MAX(FactSales[End Date ])) ))

var TotalDays = calculate(DISTINCTCOUNT('DimFiscal Calender'[Fiscal YearMonth adjusted]), FILTER(ALL('DimFiscal Calender'),AND('DimFiscal Calender'[Date]>= MIN(FactSales[Start Date ]),'DimFiscal Calender'[Date] <= MAX(FactSales[End Date ])) ))

RETURN

DIVIDE(NoOfDaysInInvoice,TotalDays) * SUM(FactSales[Amount ])

Hide this measure as we are not going to use it in actual dashboard

Create another measure with name - 

Total Sales SEK (by Start Date )

As follows 

Total Sales SEK (By Start Date) =

SUMX( 

    SUMMARIZE (

        FactSales,

        FactSales[Invoice nr],

        "Total amount by Invoice", [Total Sales SEK (by Start Date ) (hidden)])

        ,

    [Total amount by Invoice]

)

and use this measure in report

 

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[Aixia]

Hello @sayaliredij 

Yes! It works.Thanks a lot for your kind help! You´re amazing!

Regards

Aixia

[sayaliredij]

Hello @Aixia 

Is it possible to share the PBIX file ? so that i can check in details where is the problem

Thanks,

sayali

[Aixia]

Hello @sayaliredij 

Is that ok that I send you the example of sale data so that you can  do more research since I

can´t share the PBIX file due to the business issues? 

The fiscal year is September - August. 

Is that possible that I just send the data to you only?

Br/Aixia

[sayaliredij]

Hi @Aixia 

Yes thats also ok.. 

You can also prepare small PBIX file with example data. so that i know what filters you are applying. Thats important for me to understand why measure is not working the way its working for me

Thanks,

Sayali

[Aixia]

@sayaliredij 

Sure. Now I´ve prepared the both.  

How can I send them to you? 

The correct sales amount during there fiscal year should be like this based on the sales data. 

Thanks for your kind help. It´ll be wonderful when the measure works.

Br/Aixia

[sayaliredij]

Hi @Aixia 

You can upload on dropbox or google drive and share with me

Thanks,

Sayali

[Aixia]

Hello @sayaliredij 

Now I´ve sent message to you with shared the files via Dropbox . Thanks a lot. 

Br/Aixia

[sayaliredij]

Hello @Aixia 

1. Make all relations inactive. 

then the measure should work.

2. For Total amount by Invoice date, please make following change

Total sales SEK (by Invoice Date) = CALCULATE(SUM(FactSales[Amount ]),USERELATIONSHIP('DimFiscal Calender'[Date],FactSales[Invoice Date]))

3. Remove this weird number from the file 

I think then it should work

https://www.dropbox.com/scl/fi/xfb0es3141t8sxi1w0cbs/Sales.pbix?rlkey=w6ayk2rg1xebodkmjdenigkus&dl=0

Thanks,

Sayali

[Aixia]

Hello @sayaliredij 

I´ve just shared with you the updated Sales data. 

I did as you suggested and the measure works well. You´re amazing!

Now it´s just the small issues as the total amount left. Looking forward to your reply. Thanks.

Br/Aixia

[Aixia]

Hello @sayaliredij 

I´ll check & get back to you. Thanks a lot!

Br/Aixia

[sayaliredij]

Hello @Aixia 

Can you try following solution

1. Make all relations ships between Fact Sales and calendar data inactive

2. Try following DAX for Total Sales By Start date (make sure you have monthYear eg 202001 in your date table)

Total Sales by StartDate =

var NoOfDaysInInvoice = calculate(DISTINCTCOUNT(Datetable[MonthYear]), FILTER(dateTable,AND(dateTable[Date] >= MIN(sales[StartDate]),dateTable[Date] <= MAX(sales[EndDate])) ))

var TotalDays = calculate(DISTINCTCOUNT(Datetable[MonthYear]), FILTER(ALL(dateTable),AND(dateTable[Date] >= MIN(sales[StartDate]),dateTable[Date] <= MAX(sales[EndDate])) ))

RETURN

DIVIDE(NoOfDaysInInvoice,TotalDays) * SUM(Sales[Amount SEK])

i have been getting correct answer as you expecting

 I hope that helps

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[Aixia]

Hi @sayali 

Now I´m trying to do as your suggested. 

1. About the date ´´MonthYear´´ as you suggestd.

    In this case the fiscal year was September-August, so the Fiscal Year shows as 2020-21 in date table. So I did two steps in order to get the column ´´MonthYear´´ 

    step 1:  I created the new column ´´ AdjustedYear`` which shows as 2021 for the fiscal year 2020-21;

    step 2: one more new column ´´ Fiscal YearMonth adjusted´´ which shows as 202109 for the fiscal year spetember 2020;

    Note:  it may not the best solution for the step 2 because it will make some confusions. In this case, 202109 actually was 202009. I don´t know how to solve it. Do you have any suggestions for that?

2. about the creating relationships.

In your exampel below, I want to know relationships between Fiscal Year and and MonthYear to which date in Sales table: invoice date?  start date ? end date?

3. For the measure part, I tried to write measure like below. But I got lots of warnings before I complete the whole measure as you suggested.

  1) One warn for the VAR NoofDaysInInvoice. Why?

  2) for the second Variables: 

      2-a) warning for the VAR TotalDays when I wore the namn already ?!

      2-b) complain a lot for the AND function, the DimFiscal calender table couldn´t show up at all. So I made a copy from the FILTER on first VAR  and pasted it to the second VAR, it didn´t work neither.

I´ve checked my measure with yours, it should be right. 

What do you think why the measure didn´t work in this case?

This case is more complcated because of the fiscal year. And I like your solution.

Could you please help me again? Thanks.

Br/Aixia

[sayaliredij]

Hello @Aixia 

1. In this scenario, you can simply utilize the month and year from the current date.

Generate a calculated column named MonthYear:

MonthYear =

MonthYear = (Year(DateTable[Date]) * 100) + Month(DateTable[Date])

2. 

All relationships in this context are directed towards the 'Date' entity.

The connections are as follows:

• Invoice Date points to Date
• StartDate is linked to Date
• EndDate is associated with Date

3. PLease delete and copy again from my answer.  there are some extra brackets in your DAX

Thanks,

Sayali

[Aixia]

Dear @sayaliredij 

Hope you´re fine.

With your kind help, there are only a few steps left until that problem is fixed. 

On Thursday last week I´ve asked you the issues regarding the last steps. Please see the message 10 of 10.

Could you please take your time to take a look on that? 

Thnaks a lot.

regards/Aixia

[Aixia]

Hello @sayaliredij 

As a new beginner, feel great to have your support. Thanks.

Now there are two issues left which I appreciate to get your help again.  

1. I saw that you got the corect answer to the earlier question. But I got only correct amount to the FY 2021-22. Nothing to the FY 2022-23.

Yours:

Mine:

2. Missing the amount to the FY 2023-24.

The sum of 2021-22 & 2022-23 is 27 308 117( = ( 206 509+27 101 608 ) and the Totalt is 47 647 460. There is difference on amount 20 339 343 (=47 647 460 - (27 308 117).

The difference depends on partly the missing amount from the problem above, partly the amount belongs to the Fiscal year 2023-24.

Example below:

Invoice 22061 refers to the period 20230101-20231231. That is say, 8/12 of 811 032 SEK =540 688 )  belongs to FY 2022-23 and 4/12 ( = 270 344 ) belongs to FY 2023-24. 

But when I choose the fiscal year 2023-24, there is no amount under Total sales. 

The measure should work for all the FY, right?  

In this case, I have FiscalCalender Date table within the period below.

Could you please help me why the sales amount was missing for the FY 2023-24? 

Thanks!

Regards

Aixia

[Aixia]

Hi again @sayaliredij 

I´ve sent you three questions ealier regarding your solutions. Now I tried again and it seems works mostly 🙂

To the Q1. Now the Fiscal YearMonth adjusted looks fine .

To the Q2: It seems that I´ve alreday connected three dates on FactSales table( by invoice date, by start date och by due date) to the DimFiscal calender table. 

To the Q3: This time I just copied your whole measure Total Sales SEK (by Start Date) & changde the table and colomn namn to my own. And it seems work for FY 2021-22 & 2022-23. See below. 

We can see that the sum of 2021-22 & 2022-23 is 27 308 117( = ( 206 509+27 101 608 ) and the Totalt is 47 647 460. The difference amount is 20 339 343 (=47 647 460 - (27 308 117) which belongs to the Fiscal year 2023-24. 

There are some invoices which also need to divide the Amount to FY 2023-24. 

Example below: 8/12 of 811 032 SEK belong to FY 2022-23 and 4/12 part belong to FY 2023-24.  

But when I choose only the fiscal year 2023-24, there is no amount under Total sales. 

The measure should work for all the FY, right?  

In this case, I have FiscalCalender Date table within the period below.

Could you please help me why the sales amount was missing for the FY 2023-24? 

Thanks a lot.

Br/Aixia

[Aixia]

Hello Sayali

I´ll try. Thanks a lot in advance.

Br/Aixia

[Aixia]

Hello 

I am struggling with how to solve the problem. I appreciate if I get any suggestions on the measures. 

Is there anyone who can help me? Thanks.

Regards/Aixia

[sayaliredij]

Hi @Aixia 

I have question for you. as per your comment - Sales amount for 2021-23 shows 2 478 112:-.

but as per the picture its allocated to 2021-22 fiscal year. which seems to be correct as the start date fall in august 2022 it mean it falls into q4 of 2021-22 

is there something missing ?

Thanks,

Sayali