Forum Discussion

ERICCFG's avatar
ERICCFG
Frequent Visitor
1 year ago
Solved

Sage 300 date overlap problem.

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.

  • ERICCFG's avatar
    ERICCFG
    1 year ago

    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

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

     

  • some_bih's avatar
    some_bih
    Community Champion
    1 year ago

    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.

      • some_bih's avatar
        some_bih
        Community Champion
        1 year ago

        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? 

      • some_bih's avatar
        some_bih
        Community Champion
        1 year ago

        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)