Measure to sum data for first available month of every year

[Anonymous]

Hi, I am relatively new to PowerBI and DAX programming (although I have been working on Tabular Cubes a little bit).

Anyway, I want to create a measure which will have sum(field) for the first available month of the year.

The table below should help to understand better.

Thanks to all of you in advance.

Period	SUM(Field) for a month	DESIRED OUTPUT: SUM(Field) for first available month of the year (MIN)
2018-11	56	56
2018-12	51	56
2019-01	85	85
2019-02	12	85
2019-03	65	85
2019-04	24	85
2019-05	87	85
2019-06	15	85
2019-07	63	85
2019-08	38	85
2019-09	17	85
2019-10	99	85
2019-11	18	85
2019-12	17	85
2020-01	33	33
2020-02	41	33

[Greg_Deckler]

I did it like this:

PBIX is attached. 

Measure = 
    VAR __Year = YEAR(MAX('Table'[Period]))
    VAR __Table = FILTER(ALL('Table'),YEAR('Table'[Period]) = __Year)
    VAR __Min = MONTH(MINX(__Table,[Period]))
RETURN
    SUMX(
        FILTER(__Table,MONTH([Period]) = __Min),
        [Field]
    )

[Anonymous]

Hi, it worked like a charm for total, however, if I add some filter (narrow down data), new measure shows total (not narrowed down as all other measures). Maybe there is a small twerk to change it so that it would work on whichever filters I would apply?

[Anonymous]

Create year column and month column.

Then create table

Table = SUMMARIZE(Sheet3,Sheet3[Year],"Month",MIN(Sheet3[Month]))

Create amount column in table as 

amount = LOOKUPVALUE(Sheet3[Amount],Sheet3[Month],'Table'[Month],Sheet3[Year],'Table'[Year])

Then join sheet3 and table on year column.

And drag amount column from table and year and month from sheet3.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[ibarrau]

Hi, what a nice unusual question haha. Let me see. The measure I'll show you will only work if you add it in a visualization with the Date (period column). Otherwise you should mention your objetive for the visualization or we should consider using a column instead of measure.

Try this:

Measure =
VAR year_actual_line = YEAR(MAX(Table[Period]))
RETURN
CALCULATE (
    SUM ( Table[Field]) ) ,
    YEAR( Table[Period] ) = year_actual_line ,
    MONTH ( Table[Period] ) = 1
)

Hope I get the idea and this works,

Regards,

[Anonymous]

Hi, your suggested solution showed data only for the first month, I added ALLEXECPT( ) filter, it fixed it, BUT

if I add some filter (narrow down data), new measure shows total (not narrowed down as all other measures). Maybe there is a small twerk to change it so that it would work on whichever filters I would apply?

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

I created a [Year] column and a [Month] column first;

Year = [Period].[Year]
Month = [Period].[MonthNo] 

Then, I create a [Sum_Field] measure;

Sum_Field = 
CALCULATE(
    SUM(Sheet1[Field]),
    ALLEXCEPT(
        Sheet1,
        Sheet1[Year], Sheet1[Month]
    )
) 

At last, I wrote a measure referring to @Greg_Deckler‘s answer;

Measure 2 = 
VAR x = MAX(Sheet1[Year])
VAR y = FILTER(ALL(Sheet1), Sheet1[Year] = x)
var z = MINX(y, Sheet1[Month])
RETURN
SUMX(
    FILTER( y, Sheet1[Month] = z),
    [Sum_Field]
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

You can try the solution which i have posted.

Thanks
Pravin