comparing last month, previous and last month-12 (same month previous year) -DAX

[cosminc]

Hi

i need to buid a graph top salesman rox and legend month - and need to be fixed with max month, max month-1 and max month-12,

i use a calendar (1 jan 2017 till 31 dec 2019) connected with the data base

let's take a simple example

salesman    sales  year  month

thanks!

Cosmin

[Greg_Deckler]

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

[cosminc]

Hi Greg,

i didn't find what i need 

if you have 2 minutes it would be very helpful for me, i'm stuck on this

i want to built a measure like this:

Month Comparison =

VAR MaxYear = CALCULATE(MAX(Source[Year]), ALL(Source))

VAR MaxMonthNr = CALCULATE(MAX(Source[Year Month Number]), Source[Year] <= MaxYear)

RETURN

CALCULATE(SUM(Source[Sales), FILTER(Source, Source[Year Month Number] = MaxMonthNr && MaxMonthNr -1 && MaxMonthNr -12))

i need to make a fixed graph (without filters, but updatable with new data) with top 10 salesmen (as example) after sales from MaxMonthNr (last month with data), but also i need to put on graph previous month and same month last year for comparisons

so a need t put on bar graph - Salesmen - axis, the measure from above as Legend and Sales on Value.

More than that a need a measure to filter top 10 salesmen after Sales from MaxMonthNr not top 10 after all 3 months.

i also have a calendar but on this situation i think is not proper to use it, there last month is december 2019 not february 2019 like on my data base.

help me please with the right syntaxes

Thanks a lot!

Cosmin 

[Ashish_Mathur]

Hi,

Show a sample dataset and also the expected result.  Just show your expected result in a Table format.  From there, we can always change the visual to a graph.

[cosminc]

Hi,

Data series, objectives and expected result are put below:

if it's helpfull i use a calendar till 31-12-2019 in relationship with the data base

thanks a lot!

Cosmin 

Year	MonthNo	Salesman	Sales
2019	2	George	59
2019	1	George	68
2018	1	George	71
2018	2	George	94
2018	3	George	89
2018	4	George	83
2018	5	George	53
2018	6	George	91
2018	7	George	83
2018	8	George	51
2018	9	George	78
2018	10	George	64
2018	11	George	88
2018	12	George	99
2017	1	George	97
2017	2	George	93
2017	3	George	100
2017	4	George	82
2017	5	George	92
2017	6	George	65
2017	7	George	90
2017	8	George	52
2017	9	George	99
2017	10	George	80
2017	11	George	67
2017	12	George	95
2019	1	Alex	59
2018	1	Alex	86
2018	2	Alex	50
2018	3	Alex	65
2018	4	Alex	79
2018	5	Alex	63
2018	6	Alex	94
2018	7	Alex	97
2018	8	Alex	96
2018	9	Alex	98
2018	10	Alex	97
2018	11	Alex	80
2018	12	Alex	92
2017	1	Alex	87
2017	2	Alex	79
2017	3	Alex	57
2017	4	Alex	61
2017	5	Alex	88
2017	6	Alex	77
2017	7	Alex	62
2017	8	Alex	78
2017	9	Alex	60
2017	10	Alex	55
2017	11	Alex	63
2017	12	Alex	57
2018	2	Teo	88
2018	3	Teo	56
2018	4	Teo	85
2018	5	Teo	80
2018	6	Teo	86
2018	7	Teo	73
2018	8	Teo	51
2018	9	Teo	67
2019	2	Jenna	93
2019	1	Jenna	63
2018	1	Jenna	100
2018	2	Jenna	78
2018	3	Jenna	73
2018	4	Jenna	73
2018	5	Jenna	74
2018	6	Jenna	58
2018	7	Jenna	93
2018	8	Jenna	97
2018	9	Jenna	100
2018	10	Jenna	59
2018	11	Jenna	84
2018	12	Jenna	78
2017	1	Jenna	84
2017	2	Jenna	66
2017	3	Aimee	72
2017	4	Aimee	77
2017	5	Aimee	53
2017	6	Aimee	98
2017	7	Aimee	99
2017	8	Aimee	98
2017	9	Aimee	80
2017	10	Aimee	60
2017	11	Aimee	58
2017	12	Aimee	92
2019	1	Aimee	59
2018	1	Aimee	86
2018	2	Aimee	63
2018	3	Jason	61
2018	4	Jason	94
2018	5	Jason	86
2018	6	Jason	53
2018	7	Jason	81
2018	8	Jason	94
2018	9	Jason	51
2018	10	Jason	70
2018	11	Jason	52
2018	12	Jason	91
2017	1	Jason	73
2017	2	Jason	62
2017	3	Jason	65
2017	4	Jason	97
2017	5	Jason	55
2017	6	Jason	57
2017	7	Jason	72
2017	8	Jason	53
2017	9	Jason	72
2017	10	Jason	87
2017	11	Jason	50
2017	12	Jason	72
2018	2	Jason	70
2018	3	Jason	68
2018	4	Jason	84
2018	5	Jason	62
2018	6	Jason	53
2018	7	Jason	91
2018	8	Jason	79
2018	9	Jason	94

[v-juanli-msft]

Hi @cosminc 

I replied your lastest post.

https://community.powerbi.com/t5/Desktop/index-dax/m-p/631023#M301675

Does my solution solve that case?

If my solution help, to get this visual to show only top5 salesman, just add "rankx" meausre in the "Visual level filter" and select "less than 6".

For this case, i have something unclear.

If you still have problem, could you kindly make me clear?

From you screenshot, i don't know which you select from the slicer, year=2019, month=2 ???

If you select (year=2019,month=2), then the visual should show

(year=2019,month=1; year=2019,month=2; year=2018,month=2), right???

Best Regards

Maggie

[cosminc]

hi

i want to make a fixed graph not to select manually with the slicer

so when i'll have also data for march i want to see on the graph 2019 - 3 (march), 2019 - 2 (feb) and 2018 - 3 (march previous year)

think at it like a presentation without slicers on it, which will be updated automatically

thanks,

Cosmin

[v-juanli-msft]

Hi @cosminc 

It seems possible.

This workaround is based on the following assumption.

assume today=2019/2/27, then you have data until 2019/2

if today is 2019/3/2, then you have data until 2019/3

screenshot1->when today=2018/2/27

screentshot2->when today=2019/3/2 (from screenshot1 to2, just refresh the data when coming to a new month).

then i work as below

create a calculated column

year-month = [Year]&"-"&[MonthNo]

create measures

flag1 =
IF (
    (
        YEAR ( TODAY () ) = MAX ( Sheet7[Year] )
            && (
                MONTH ( TODAY () ) = MAX ( Sheet7[MonthNo] )
                    || MONTH ( TODAY () ) - 1
                        = MAX ( Sheet7[MonthNo] )
            )
    )
        || (
            YEAR ( TODAY () ) - 1
                = MAX ( Sheet7[Year] )
                && MONTH ( TODAY () ) = MAX ( Sheet7[MonthNo] )
        ),
    1,
    0
)

flag2 = IF(YEAR(TODAY())=MAX(Sheet7[Year])&&MONTH(TODAY())=MAX(Sheet7[MonthNo]),1,0)

sales_this month = CALCULATE(SUM(Sheet7[Sales]),FILTER(ALLEXCEPT(Sheet7,Sheet7[Salesman]),[flag2]=1))

sales new = CALCULATE(SUM(Sheet7[Sales]),FILTER(ALLEXCEPT(Sheet7,Sheet7[Salesman],Sheet7[year-month]),[flag1]=1))

new ranks = RANKX(FILTER(ALL(Sheet7),[flag1]=1),[sales_this month],,DESC,Dense)

See my pbix for more details.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cosminc]

Hi

Thanks a lot

the idea is that i update the base monthly with previous month (on 4 march i updated with feb 2019) and the calendar is up to 31 dec 2019

the data base is on monthly level (i made an improvisation to have a day column with first day of each month and this way to put in relationship with the calendar) and slicers are on year and month level; i have in mind that column year - month would be useful on this case - base on it a measure with filter max year-month number, -1 and -12

i will analize your solution and maybe i can adapt it to the situation

thanks

Cosmin

[Ashish_Mathur]

Hi,

Perhaps someone else will help you.  I do not understand the meaning of "Top 5 salesman after sales of last month"?

[cosminc]

Hi,

imagine that you make a top (ranked) with salesmen after their sum of sales

but after their last sales - from feb 2019 in this case 

thanks!