How to show value for all months between begin and end date

[Anonymous]

Hi all'ya.

Data comes in as follows:
Project name, beginning date for billing, end date  for billing, total sum and monthly revenue. Each of these is their own field. How do I show on the report that, e.g. billing that started March and ended in May shows values for all these three months?

Can I do this in DAX or should I spread dates in query? And if the latter what do I do with nulls? I quess I could just insert project start date as value for nulls.

[v-rzhou-msft]

Hi @Anonymous ,

I suggest you to create an unrelated calendar table and add a measure to filter the visual.

Data model:

Measure:

Filter = 
VAR _RANGESTART =
    MIN ( 'Calendar'[Date] )
VAR _RANGEEND =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        MAX ( 'Table'[beginning date for billing] ) >= _RANGESTART
            && MAX ( 'Table'[end date for billing] ) <= _RANGEEND,
        1,
        0
    )

Add this measure into visual level filter and set it to show items when value =1.

Best Regards,
Rico Zhou

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

[Anonymous]

Problem is, I want to show the months between - not just amount of months or the first and the last. But I want to list all the months between.

[Anonymous]

Yes, thank you so much. I'll look at this with more thought tomorrow.