Calculation for latest month according to selection

[Anonymous]

Hello world, 
new to DAX and Power bi here, I have tried to create a dynamic graph that would give the user the ability to see the difference between current month and previous month, but with a twist.!The user can select different year/month through another graph, so I want the graph to be updated. 
So...I have created this calculation for the current month:

This Month Count = CALCULATE([Count],FILTER('Time Table','Time Table'[Month Number]==MONTH(TODAY())),filter('Time Table','Time Table'[Year]=YEAR(TODAY())))

Previous Month Count = CALCULATE([Count],FILTER('Time Table','Time Table'[Month Number]=MONTH(TODAY())-1),filter('Time Table','Time Table'[Year]=YEAR(TODAY())))

But this work only for the latest month of course. 
Check the printscreens, maybe you get a better idea. 
So the question is..How do we create a dynamic graph that shows the currently selected month vs the previous to selected month and if nothing is selected we see the current month vs the previous month.?
Thanks everyone.!

This is the default now

 This is after the selection, where I want to be updated to show the updated data.

[v-shex-msft]

HI @Anonymous,

You can try to use the following measure formula if they meet for your requirements:

Selected/Current Month Count =
IF (
    COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
    CALCULATE (
        [Count],
        ALLSELECTED ( 'Time Table' ),
        VALUES ( 'Time Table'[Date] )
    ),
    CALCULATE (
        [Count],
        FILTER (
            ALLSELECTED ( 'Time Table' ),
            'Time Table'[Month Number] = MONTH ( TODAY () )
                && 'Time Table'[Year] = YEAR ( TODAY () )
        )
    )
)


Previous month of Selected/Current Month Count =
VAR selected =
    MAX ( 'Time Table'[Date] )
VAR prevSelected =
    DATE ( YEAR ( selected ), MONTH ( selected ) - 1, DAY ( selected ) )
VAR prevCurrent =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
        CALCULATE (
            [Count],
            FILTER (
                ALLSELECTED ( 'Time Table' ),
                'Time Table'[Year] = YEAR ( prevSelected )
                    && 'Time Table'[Month Number] = MONTH ( prevSelected )
            )
        ),
        CALCULATE (
            [Count],
            FILTER (
                ALLSELECTED ( 'Time Table' ),
                'Time Table'[Year] = YEAR ( prevCurrent )
                    && 'Time Table'[Month Number] = MONTH ( prevCurrent )
            )
        )
    )

BTW, you can also take a look at the following link about use date function to manually define filter range for calculating.

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for your answer.!This seems like something that it could work, although I have no 'Time Table'[Date] field, my only fields available are 
Month Number
Year Number 
Month ID - which is a field like 202001 to show 2020/01. 
Likewise for the days/weeks/quarters.
Well, my only issue is that I have dates that are further than the one today (e.g I have dates in 2022) that have no values for the Count field. Is there any way to obtain the max date that I have data for measure Count?

Also, there's also the issue of January...!e.g if the user selects January of 2018, then the comparison should be made between January 2018 and December 2017, which I think this aforementioned calculation doesn't take into account, as it calculates the Year, maybe with an if in January, it would be more complete.
Unfortunately I can upload no data, sorry.!

[Anonymous]

Right now I have created this

Metric 1 = 
VAR LatestYearRollingNo =
    CALCULATE ( IF(ISFILTERED('Time Table'[Month Name]),FIRSTNONBLANK('Time Table'[Year],'Time Table'[Year]),year(TODAY())),
        ALLSELECTED ( 'Time Table'[Year] )
    )
RETURN
VAR LatestMonthRollingNo =
    CALCULATE ( IF(ISFILTERED('Time Table'[Month Name]),FIRSTNONBLANK('Time Table'[Month Number],'Time Table'[Month Number]),month(TODAY())),
        ALLSELECTED ( 'Time Table'[Month Number] )
    )
RETURN
    CALCULATE (
        [AR Receipt Count],
        'Time Table'[Month Number]= LatestMonthRollingNo,
        'Time Table'[Year]=LatestYearRollingNo
    )

which works great to show me the selected the month+the last one by default, but I cannot create a similar calculation to show me the previous of the selected month.

Any ideas @v-shex-msft ??

[v-shex-msft]

HI @Anonymous,

Can you please share some dummy data with minimum data structure? It will help to test and coding formulas.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

I think this is good enough data to check calcs, please tell me if you wnt more (random number+fake ids, but all the others are ok)

https://drive.google.com/open?id=12uuaGTjAl2I48EjSu3jjX3JPsQ9Z7eB64PgslIUW3Tw

Check the data and let's see what we got.!
Thanks @v-shex-msft !

[v-shex-msft]

HI @Anonymous,

You can try to use the following measure formulas if they suitable for your requirement:

Selected/Current Month Count =
IF (
    COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
    CALCULATE (
        [Count],
        ALLSELECTED ( 'Time Table' ),
        VALUES ( 'Time Table'[Fiscal Month] )
    ),
    CALCULATE (
        [Count],
        FILTER (
            ALLSELECTED ( 'Time Table' ),
            'Time Table'[Fiscal Month Number] = MONTH ( TODAY () )
                && 'Time Table'[Fiscal Year] = YEAR ( TODAY () )
        )
    )
)

Previous month of Selected/Current Month Count =
VAR selected =
    MAX ( 'Time Table'[Fiscal Month] )
VAR formatted =
    DATE ( LEFT ( selected, 4 ), RIGHT ( selected, 2 ), 1 )
VAR prevSelected =
    DATE ( YEAR ( formatted ), MONTH ( formatted ) - 1, DAY ( formatted ) )
VAR prevCurrent =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
RETURN
    IF (
        COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
        CALCULATE (
            [Count],
            FILTER (
                ALLSELECTED ( 'Time Table' ),
                'Time Table'[Fiscal Year] = YEAR ( prevSelected )
                    && 'Time Table'[Fiscal Month Number] = MONTH ( prevSelected )
            )
        ),
        CALCULATE (
            [Count],
            FILTER (
                ALLSELECTED ( 'Time Table' ),
                'Time Table'[Fiscal Year] = YEAR ( prevCurrent )
                    && 'Time Table'[Fiscal Month Number] = MONTH ( prevCurrent )
            )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Eventually I followed another path, and this is the solution I found
this is the code for the current month/selected month

Same year Current Month = 
VAR selectedyear =
    CALCULATE (
        IF (
            ISFILTERED ( 'Time Table'[Year] ),
            FIRSTNONBLANK (
                'Time Table'[Year],
               'Time Table'[Year]
            ),
            YEAR ( TODAY () )
        )
    )
RETURN
    VAR selectedmonth =
        CALCULATE (
            IF (
                ISFILTERED ( 'Time Table'[Month Name] ),
                FIRSTNONBLANK (
                    'Time Table'[Month Number],
                    'Time Table'[Month Number]
                ),
                MONTH ( TODAY () )
            )
        )
    RETURN
        CALCULATE (
            [Count],
            'Time Table'[Year] = selectedyear,
            'Time Table'[Month Number] = selectedmonth,
            ALLSELECTED ( 'Time Table'[Month Number] ),
            ALLSELECTED ( 'Time Table'[Year] )
        )

 this is the one for the previous to selected,with a provision for january. 

Same Year Prev Month = 
VAR selectedyear =
    CALCULATE (
        IF (
            ISFILTERED ( 'Time Table'[Year] ),
            FIRSTNONBLANK (
                'Time Table'[Year],
                'Time Table'[Year]
            ),
            YEAR ( TODAY () )
        )
    )
RETURN
    VAR selectedmonth =
        CALCULATE (
            IF (
                ISFILTERED ( 'Time Table'[Month Name] ),
                FIRSTNONBLANK (
                    'Time Table'[Month Number],
                    'Time Table'[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 (
                    [Count],
                    'Time Table'[Year] = selectedyear2,
                    'Time Table'[Month Number] = selectedmonth2,
                    ALLEXCEPT (
                        'Time Table',
                        'Time Table'[Day Number]
                    )
                )

For some reason that I haven't yet figured out this one works unlike the solution of @v-shex-msft. It must be an issue of our data rather than the solution itself. 
Nonetheless thank for your help and inspiration!

[Anonymous]

A nice implementation i have seen is this

Metrics = 
VAR LatestMonthRollingNo =
    CALCULATE (
        MAX ( 'Time Table','Time Table'[Month] ),
        ALLSELECTED ( 'Time Table','Time Table'[Month] )
    )
RETURN
VAR LatestYearRollingNo =
    CALCULATE (MAX ('Time Table','Time Table'[Year] )),
        ALLSELECTED ('Time Table','Time Table'[Year] )
    )
RETURN
    CALCULATE (
        [Counter],
        'Time Table','Time Table'[Month]= LatestMonthRollingNo,
        'Time Table','Time Table'[Year]=LatestYearRollingNo
    )

 but unfortunately this doesn't work when I haven't selected any month because (most probably) there are dates for next month, but those dates contain no data on the measure I want to count. So a mix of the previous calc +this one is needed I think but I got no clue how to make the mix.!