Forum Discussion

Helper IV

9 years ago

I need help

Mesure 2015-2016 = CALCULATE(SUM(Factures[2015-2016]);FILTER(ALL(Factures);Factures[Mois.Date] >= DATE(2015;07;01) && Factures[Mois.Date] <= MAX(Factures[Mois.Date]))) Bonjour, J'aimerais rempla...

Need Help

AlexGallet01

Helper IV

9 years ago

Or how to cumulate with this formula ?

Mesure = CALCULATE(SUM(Factures[2004-2005]);FILTER(Factures;Factures[Mois.Date] <= DATE(2005;06;31)))

Vvelarde

Community Champion

9 years ago

hi AlexGallet01

Try in this way:

1. A Calendar table with Fiscal Year, and Fiscal Month Number Columns

Fiscal Year =
IF ( MONTH ( 'Calendar'[Date] ) <= 6, 'Calendar'[Year], 'Calendar'[Year] + 1 )

Fiscal Month =
IF (
    MONTH ( 'Calendar'[Date] ) <= 6,
    'Calendar'[Month] + 6,
    'Calendar'[Month] - 6
)

2. A new Measure

ValuesThisYear =
CALCULATE (
    SUM ( Table1[Value] ),
    DATESBETWEEN (
        'Calendar'[Date],
        CALCULATE (
            FIRSTDATE ( 'Calendar'[Date] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Fiscal Year] = MIN ( 'Calendar'[Fiscal Year] )
            )
        ),
        LASTDATE ( 'Calendar'[Date] )
    )
)

3. Insert a slicer

4. Insert a Visual Clustered Column Chart

5. If you need to sort the months Select MonthN and Sort by Fiscal Month

AlexGallet01
Helper IV
9 years ago
Hi

I have a problem when i want create a column fiscal year i have an error ( Expressions that return a variant data type can not be used to define computed columns.)
AlexGallet01
Helper IV
9 years ago
I try to use your solution but i don't have a cumulate result
AlexGallet01
Helper IV
9 years ago
I use this formula

test = CALCULATE(SUM(Factures[MontantFacture]);DATESBETWEEN(Calendrier[Date].[Date];CALCULATE(FIRSTDATE(Calendrier[Date].[Date]);FILTER(ALL(Calendrier);Calendrier[AnnéeFiscal]=MIN(Calendrier[AnnéeFiscal])));
LASTDATE(Calendrier[Date].[Date])))
v-qiuyu-msft
Community Support
9 years ago
AlexGallet01 wrote:

Hi

I have a problem when i want create a column fiscal year i have an error ( Expressions that return a variant data type can not be used to define computed columns.)

Did you try to create a calculated column like this?

Fiscal Year = IF ( MONTH ( 'Calendar'[Date] ) <= 6, 'Calendar'[Year], 'Calendar'[Year] + 1 )

Please check if the 'Calendar'[Year] data type is number rather than text. If issue persists, please share your .pbix file if possible.

Best Regards,
Qiuyun Yu