Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Problem of axis filtering/interraction with Analysis Services

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 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 Month

    Previous 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 month

    Current 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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous - 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.

       

      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/1260000

      Also, 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's avatar
        Anonymous
        Not applicable
        6 years ago

        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. 

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous , If they are a current month and previous month formula?

      I think them Time intelligence formula should work in Analysis service too

      example

      MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

       

      Check if this can work

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        hello amitchandak , for sure it cannot work as I have no date , in the sense of dateformat, the calendar is more of numbers, please check the thread above, this is also one of  the reasons I use that complex calcs for previous month etc (the other reason is that I want to have it set on default to the last month and previous to last month, and when the user clicks on the left hand side graph to adjust accordingly)