I need help

[AlexGallet01]

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 remplacer la fin de ma mesure (MAX(Facture[Mois.Date]) par une date précise mais sa ne fonctionne plus du tout lorsque je remplace cette fin par DATE(2016;06;31) et je ne comprend pas pourquoi.

Hello, I would like to replace the end of my measure (MAX (Invoice [Mois.Date]) with a specific date but it no longer works at all when I replace DATE (2016; 06; 31) and I do not understand why

[AlexGallet01]

I don't undertand how use your solution it is possible to use my measure and replace MAX(Facture[Mois.Date]) by DATE(2016;06;31) and have the same result ????

[austinsense]

Make your life a little easier and try this if you want a fixed set of dates (DATESBETWEEN) https://msdn.microsoft.com/en-us/library/ee634557.aspx

Bigger question, why do you want use fixed dates, that doesn't seem very useful for slicing and dicing?

[AlexGallet01]

Je souhaite faire un cumul de donées monétaire sur une année et ceci pour chaque années et ensuite pouvoir les comparere entre eux. Et lorsque je fais un datesbetween plus rien ne fonctionne

I wish to accumulate monetary data over a year and this for each year and then be able to compare them. And when I do a datesbetween nothing works anymore

[austinsense]

D'accord.  You should make two formulas - You'll need to have a calendar table in your model.

Current Year (YTD)

Current Year (YTD) = CALCULATE([Revenue], DATESYTD(CalendarTable[Date]) )

Last Year (YTD)

CALCULATE([Current Year (YTD)],DATEADD(Calendar[Date],-1,YEAR) )

[AlexGallet01]

pour l'instant lorsque je les met en séparé tout fonctionne normalement mais des que je souhaite les réunir sour un même c'est le drame.

For the moment when I put them separated everything works normally but of the that I wish to join them to the same one it is the drama

[austinsense]

Here's the trick - don't make a measure for every year.  Make one measure and then use a calendar table to slice the different years.

[AlexGallet01]

and i need the total for each month for each year

[AlexGallet01]

Je dois donc faire comme vous avez fait avant et en plus mes anées sont de juillet en juin de l'an d'après comment faire ?

So I have to do as you did before and in addition my years are from July to June of the following year how to do?

[austinsense]

Just give it a try 🙂

[AlexGallet01]

[AlexGallet01]

[austinsense]

Ok let me just slow this whole thing down - I would recommend getting a book and reading it. This is the best selling book on the topic (https://www.powerpivotpro.com/the-book/)

You're missing some of the fundamentals of how to put a data model together and that's messing up your formulas / making your formulas impossble.

If you wanna speak to someone who's awesome at this who also speaks french then I can make a recommendation - Tristan Malherbe is excellent.

[AlexGallet01]

Or how to cumulate with this formula ?

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

[Vvelarde]

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]

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])))

[AlexGallet01]

I try to use your solution but i don't have a cumulate result 

[AlexGallet01]

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.)

[v-qiuyu-msft]

---

@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

[AlexGallet01]

i do finish this for monday i don't have the time to read this book

[AlexGallet01]

pour l'instant lorsque je les met en séparé tout fonctionne normalement mais des que je souhaite les réunir sour un même c'est le drame.

For the moment when I put them separated everything works normally but of the that I wish to join them to the same one it is the drama