Problem of axis filtering/interraction with Analysis Services

Question

Hello world, I have created a graph that aims to show the daily difference of docs posting among two different months.
We are using analysis services so what I can do is pretty much limited.
The problem is this: when I click on the 5th month, on the left hand side , I see data until the 30th day on the right hand side.
So the 31st day of the 4th month is being filtered so I lose data and the picture is incomplete.
In general, whenever click on a month with 30 days and the previous month has 31 days, there's loss of data.
Is there a way to avoid this in Analysis Sercvices (I think this is the most important constraint here)
Thanks everyone!

greg_deckler · Answer

Anonymous&nbsp;- I'm not quite clear on this, can you post sample data?

anonymous · Answer

Weeeell , as this is on Analysis services I don't think I can upload data simple as that , but I can give you the 2 calcs I've used for Previous and Current month :Previous MonthPrevious Month = 
VAR selectedyear =
    CALCULATE (
        IF (
            ISFILTERED ( 'Dim - Dates' [Fiscal Year] ),
            FIRSTNONBLANK (
                'Dim - Dates'[Fiscal Year],
                 'Dim - Dates'[Fiscal Year]
            ),
            YEAR ( TODAY () )
        )
    )
RETURN
    VAR selectedmonth =
        CALCULATE (
            IF (
                ISFILTERED ( 'Dim - Dates'[Fiscal Month Number] ),
                FIRSTNONBLANK (
                    'Dim - Dates'[Fiscal Month Number],
                    'Dim - Dates'[Fiscal Month Number]
                ) - 1,
                MONTH ( TODAY () ) - 1
            )
        )
    RETURN
        VAR selectedmonth2 =
            CALCULATE ( IF ( selectedmonth = 0, 12, selectedmonth ) )
        RETURN
            VAR selectedyear2 =
                CALCULATE ( IF ( selectedmonth = 0, selectedyear - 1, selectedyear ) )
            RETURN
                CALCULATE (
                    [Journal Count Header],
                    'Dim - Dates'[Fiscal Year] = selectedyear2,
                    'Dim - Dates'[Fiscal Month Number] = selectedmonth2,
                    ALLEXCEPT('Dim - Dates', 'Dim - Dates'[Fiscal Day Number]
                ))and current monthCurrent Month = 
VAR selectedyear =
    CALCULATE (
        IF (
            ISFILTERED ( 'Dim - Dates'[Fiscal Year] ),
            FIRSTNONBLANK(
                'Dim - Dates'[Fiscal Year],
                'Dim - Dates'[Fiscal Year]
            ),
            YEAR ( TODAY () )
        )
    )
RETURN
    VAR selectedmonth =
        CALCULATE (
            IF (
                ISFILTERED ( 'Dim - Dates'[Fiscal Month Number] ),
                FIRSTNONBLANK (
                    'Dim - Dates'[Fiscal Month Number],
                    'Dim - Dates'[Fiscal Month Number]
                ),
                MONTH ( TODAY () )
            )
        )
    RETURN
        CALCULATE (
            [Journal Count Header],
            'Dim - Dates'[Fiscal Year] = selectedyear,
            'Dim - Dates'[Fiscal Month Number] = selectedmonth
        )

greg_deckler · Answer

Anonymous&nbsp;- Yeah but this seems like some kind of data issue unless I am wrong. Is this a custom financial calendar we are dealing with where things like EOMONTH won't work? From reading your formulas if the 31st day isn't being included it's like it isn't correctly associated with the previous month. Or is it supposed to be in the current month? I see problems in your formula spanning years. It's really hard to tell without seeing how your calendar is laid out.
&nbsp;
See if the second link helps at all
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

anonymous · Answer

Indeed the "calendar" is a custom thing, as e.g the Month numbers are indeed numbers and are not some part of a datefield, the same goes also for fiscal days, and this is why you see this whole calculation in order to get the previous and current month values.&nbsp;

greg_deckler · Answer

Anonymous&nbsp;Can you send me a screen shot of your calendar in table view/just put the columns in a table visualization. Would need to see at least 2 months of data if possible. Please&nbsp;@ me when responding or I will lose thread.

Forum Discussion

Problem of axis filtering/interraction with Analysis Services

8 Replies

Related content

Cohort analysis

Azure Analysis Service Migration Scenarios

Excel Power Query / Analysis Services authentication fails after reopening workbook

Error Querying from Analysis Services Source

Retention analysis