Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

1 chart with different type date (monthly vs daily)

hi everyone

hope you are healthy

 

i've problem to comparing actual monthly vs actual daily in 1 chart.

 

is it possible to make 1 chart with a combination of monthly and daily?

 

 

Screenshot 2022-07-11 181352.png

 

1 ACCEPTED SOLUTION

Hi,

Please check the attached pbix file.

I created a new Axis table, and applied the below measure.

 

Qty total expected result: =
VAR _currentmonthend =
    MAX ( 'Calendar'[Date] )
VAR _previousmonthend =
    EOMONTH ( _currentmonthend, -1 )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Axis table'[Monthkey] ) = _previousmonthend,
            CALCULATE (
                SUM ( Data[Quantity] ),
                FILTER ( ALL ( 'Calendar' ), 'Calendar'[End of Month] = _previousmonthend )
            ),
        CALCULATE (
            SUM ( Data[Quantity] ),
            KEEPFILTERS (
                FILTER (
                    ALL ( 'Calendar'[Date] ),
                    'Calendar'[Date] = MAX ( 'Axis table'[Datekey] )
                )
            )
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

 

Best Regards,

Community Support Team _Tang

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

Anonymous
Not applicable

awesome Kim,

thanks for help me

 

but if I use this code - SELECTEDVALUE ( 'Axis create'[Required axis] ) = "January" -the report it can be static. what if the month changes (Jan to Feb, Feb to Mar, Mar to Apr etc)? can I use code previous month Kim?

 

Hi,

Thank you for your feedback.

May I ask how the current month is selected on the report page? Is it selected by a slicer selection on the report page? Or, do you want to show all months and dates on one visualization?

Or, please share your sample pbix file's link here, and then I can try to come up with a more accurate solution.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

May I ask how the current month is selected on the report page?

only two months, in chart I want to show previous month (month to date) and current month (daily)

 

Is it selected by a slicer selection on the report page?

yes Kim, for current month I want to choose the date I like

 

do you want to show all months and dates on one visualization?

only prev month and daily current month

 

 

 

Hi,

Please check the attached pbix file.

I created a new Axis table, and applied the below measure.

 

Qty total expected result: =
VAR _currentmonthend =
    MAX ( 'Calendar'[Date] )
VAR _previousmonthend =
    EOMONTH ( _currentmonthend, -1 )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Axis table'[Monthkey] ) = _previousmonthend,
            CALCULATE (
                SUM ( Data[Quantity] ),
                FILTER ( ALL ( 'Calendar' ), 'Calendar'[End of Month] = _previousmonthend )
            ),
        CALCULATE (
            SUM ( Data[Quantity] ),
            KEEPFILTERS (
                FILTER (
                    ALL ( 'Calendar'[Date] ),
                    'Calendar'[Date] = MAX ( 'Axis table'[Datekey] )
                )
            )
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope this can provide some ideas on how to create a solution for your data model.

 

Picture1.png

 

Qty total expected result: =
IF (
    SELECTEDVALUE ( 'Axis create'[Required axis] ) = "January",
    CALCULATE (
        SUM ( Data[Quantity] ),
        TREATAS ( VALUES ( 'Axis create'[Key] ), 'Calendar'[End of Month] )
    ),
    CALCULATE (
        SUM ( Data[Quantity] ),
        TREATAS ( VALUES ( 'Axis create'[Key] ), 'Calendar'[Date] )
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors