Get the last value in the table based on Month selected (Bar and Line Chart)

[greenlover]

Hello,

Could someone help me in getting the LAST value (YTD%) of services for that month in my RAW DATA (see table below) depending on the month selected. I want to display the YTD% as line on my Bar and Line chart. (Please see below)

Bar - Month% ; Line - YTD%

Example scenarios: 

1. If I filter APRIL in my Month filter, January, Feburary, March & April bars will show. And the Line values will show April YTD % (A-0.9957; B-0.9784)

2. If I filter MARCH in my Month filter, January, February, March bars will show. And the line values will show March YTD%

(A-0.9966; B-0.9751)

Example Data: 

FCT_SERVICE

DATE	SERVICE	Month_%	YTD_%
Jan-20	A	0.9902	0.9902
Jan-20	B	0.9555	0.9555
Feb-20	A	1.0000	0.9949
Feb-20	B	0.9758	0.9653
Mar-20	A	1.0000	0.9966
Mar-20	B	0.9939	0.9751
Apr-20	A	0.9929	0.9957
Apr-20	B	0.9885	0.9784

DIM_DATE

MONTH_NAME

YEAR

Thank you so much! 

[Anonymous · Accepted solution]

HI @greenlover,

If you want to use slicer to achieve the selector effect, I think you need to use an unconnected date table as source of the slicer.

After these steps, you can write a variable table with a summary function to summary table records based on extract selections date values and filter by the current group. Then you can get the correct cumulative total based on these filters.

measure =
VAR selected =
    MAX ( selector[Date] )
VAR summary =
    SUMMARIZE (
        CALCULATETABLE (
            Table,
            FILTER (
                ALLSELECTED ( Table ),
                YEAR ( [Date] ) = YEAR ( selected )
                    && [Date] <= selected 
            ),
            VALUES ( Table[SERVICE] )
        ),
        [Date],
        [SERVICE],
        "YTD", [YTD_%]
    )
RETURN
    MAXX ( summary, [YTD] )

Regards,

Xiaoxin Sheng

[amitchandak]

@greenlover , Not very clear on the formula you need. But with the date table, you can try

YTD Sales = CALCULATE([YTD %],DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE([YTD %],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE([YTD %],DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE([YTD %],DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE([YTD %]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

MTD

MTD Sales = CALCULATE([YTD%],DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE([YTD%],DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last year MTD Sales = CALCULATE([YTD%],DATESMTD(dateadd('Date'[Date],-12,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[greenlover]

Hi @amitchandak , 

Not working on the YTD and MTD. I just want to get the last value (YTD%) for that month in my excel file depending on the month selected and display it as a line in my bar and line visual. 

Thank you

[FarhanAhmed]

You need to make sure that your model has Date Table which is connected to your "FCT_Service" table.

then you need to do 

1- Create a reference month column from Date Table in to FCT_Service Table using  RELATED (Date[Month])

2- Create a measure 

Measure = CALCULATE(LASTNONBLANK(FCT_SERVICE[YTD%],FCT_SERVICE[YTD%]),DATESINPERIOD('date'[Date],MAX('date'[Date]),-1*(MONTH(MAX('date'[Date]))) ,MONTH   ))

3-  create a bar chart and bring Month Column from "FCT_Service" and bring this measure in values.

4- In slicer/filters put month from Date Table

Hopefully this will help you build what you want.

[greenlover]

Hello @FarhanAhmed , 

For: 

1- Create a reference month column from Date Table in to FCT_Service Table using  RELATED (Date[Month])  --> I already have a relationship between my DIM CALENDAR and FCT_Service based on date_key will that suffice? 

Thank you! 

[FarhanAhmed]

@greenlover 

Yes this is the basic requirement but If you put Month from Date Calendar in Visual, it will show only 1 month. 

i.e. If you select March then it will do a YTD upto march and show only 1 number.

When you create a reference column in Fact Table and use that month in Visual, it will evaluate on every Month and hence you will see all three months.

[harshnathani]

HI @greenlover ,

You can check if this works

Last Sales = LASTNONBLANKVALUE(Table[Date],[YTD%])

Regards,

Harsh Nathani