topic Re: Sage 300 date overlap problem. in DAX Commands and Tips

Sage 300 date overlap problem.

ERICCFG — Thu, 13 Feb 2025 20:33:32 GMT

Hello, I have a date overlap problem. I have an accounting entry table (sage 300) where there is a date column, fiscal year, period etc. In some cases the accounting entry date is the same for two periods and that means that when we want to do a filter by period and year in a calendar, we end up with false sums of expenses or income. Is there anyone who has had the same problem and how they resolved this situation.

Re: Sage 300 date overlap problem.

Rupak_bi — Fri, 14 Feb 2025 06:46:54 GMT

Hi ERICCFG
Please share sample table and elaborate the issue

Re: Sage 300 date overlap problem.

Anonymous — Fri, 14 Feb 2025 07:19:46 GMT

Hi ERICCFG ,

Can you give some simple examples? It should be possible to come up with the correct result by modifying dax.

In some cases the accounting entry date is the same for two periods and that means that when we want to do a filter by period and year in a calendar

Re: Sage 300 date overlap problem.

some_bih — Fri, 14 Feb 2025 07:25:51 GMT

Hi ERICCFG it could be that you have standard calendar and fiscal calendar data at same time, or more complex two general ledgers - pure accounting and fiscal one. If yes, check link for usage and creation of fiscal calendar in Power BI.

Re: Sage 300 date overlap problem.

ERICCFG — Fri, 14 Feb 2025 13:41:06 GMT

Re: Sage 300 date overlap problem.

ERICCFG — Fri, 14 Feb 2025 13:44:54 GMT

Re: Sage 300 date overlap problem.

some_bih — Fri, 14 Feb 2025 16:42:44 GMT

ERICCFG I do not understand your pictures, not famičiar wit descriptions, column names.

I hope you have Calendar/ Date table in your pbi file?

Re: Sage 300 date overlap problem.

ERICCFG — Mon, 17 Feb 2025 12:11:26 GMT

Yes I use fiscal calendar

Re: Sage 300 date overlap problem.

some_bih — Mon, 17 Feb 2025 20:50:47 GMT

Hi ERICCFG on link you can find standard calendar creation and fiscal year too. It also showing a lot of examples with measures, like sales fiscal year (search fiscal)

Re: Sage 300 date overlap problem.

ERICCFG — Tue, 18 Feb 2025 12:52:56 GMT

thank you

I found the solution,

CALCULATE(

SUM(GLACC[Valeur]),

GLACC[Type] = "2-Dépense",

TREATAS(VALUES(Calendrier[Période]), GLACC[Période]),

TREATAS(VALUES(Calendrier[Année Fiscale]), GLACC[Année Fiscale]),

ALL(Calendrier) -- Supprime le filtre sur la date

Thnank you for the people, they write me

Re: Sage 300 date overlap problem.

some_bih — Tue, 18 Feb 2025 21:10:29 GMT

Hi ERICCFG great. If some of posts here helped you mark ita as solution, so other could see it.