monthly columns with formula > one measure

[zahlenschubser]

I have a rather tricky problem - at least it's tricky for me!

I have a table with start and end date of a usage period and a column with a counter I want to sum up, based on some filtering to get monthly amounts, to show in a visual.

Currently I have - not very ideal - columns per month, which of course quickly adds up and makes the table really unwieldy.

The column for January 2023 for example is

2023-01 = if(
(START) < date(2023,2,1) && (ENDE) > date(2023,1,15)

, (counter)

, BLANK()
)

(start) and (ende) are columns with date values, (counter) is whole number.

I already have a date table, which I would ideally use to fill the months in the visual, but I couldn't manage to code a measure that would do the job correctly.

[Anonymous]

Hi @zahlenschubser ,

Please have a try.

Create a measures.

Monthly Counter = 
CALCULATE(
    SUM('Table'[Counter]),
    FILTER(
        ALL('Date'[Month]),
        'Date'[Month] >= MIN('Table'[Start]) &&
        'Date'[Month] <= MAX('Table'[End])
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[zahlenschubser]

Thanks for the reply!

What I am trying to do is to determine whether a date - from the date table, for the visual's columns and filtering - will fit the criteria when

- compared to the start date = before the first of the month after the month I am referencing
AND

- compared to the end date = after the 15th of the month I am referencing

and then sum up a value in a column in the same table as the start and end dates

[Anonymous]

Hi @zahlenschubser ,

Could you please provide more details with your desired output and pbix file without privacy information (or some sample data) ?

How to Get Your Question Answered Quickly

Best Regards
Community Support Team _ Rongtie

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

[zahlenschubser]

Sample data @ https://www.file-upload.net/download-15256367/sample.xlsx.html

The "problem" is that it's not always just one month that's "valid", otherwise I'd just coded a column to figure that out.

[Anonymous]

Hi @zahlenschubser ,

Please have a try.

Create a measure.

Measure =
VAR _start =
    EDATE ( MAX ( data[K_o_start] ), 1 )
VAR _startdate =
    DATE ( YEAR ( _start ), MONTH ( _start ), 1 )
VAR _enddate =
    DATE ( YEAR ( MAX ( data[k_o_end] ) ), MONTH ( MAX ( data[k_o_end] ) ), 15 )
RETURN
    CALCULATE (
        SUM ( data[counter] ),
        FILTER (
            ALL ( data ),
            data[K_o_start] <= _startdate
                && data[k_o_end] >= _enddate
        )
    )

How to Get Your Question Answered Quickly 

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[zahlenschubser]

That looks like it could generally work, but how does that measure calculate the results when I use it in a visual with months from my date table?

Because if I filter the visual by year and use the months for the columns I get rather strange amounts:

[Anonymous]

Hi @zahlenschubser ,

Create a date table.

Then create a month column.

month =
VAR _1 =
    MONTH ( 'calendar'[Date] )
RETURN
    SWITCH (
        _1,
        1, "January",
        2, "February",
        3, "March",
        4, "April",
        5, "May",
        6, "June",
        7, "July",
        8, "August",
        9, "September",
        10, "October",
        11, "November",
        12, "December",
        "Unknown month number"
    )

Best Regards
Community Support Team _ Rongtie

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

[zahlenschubser]

I was using [month] from the date hierarchy from my date table, how is this different?

[Anonymous]

Hi @zahlenschubser ,

You can also create a month column from the date table.

Best Regards
Community Support Team _ Rongtie

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

[zahlenschubser]

No, what I mean is - why is the result different if I make an actual column for the month versus using the generated [month] field from the date hierarchy?