Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

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

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.
3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Anonymous ,

 

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

Data model:

vrzhoumsft_0-1695886221436.png

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.

vrzhoumsft_1-1695886262425.png

 

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.

 

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)