How to pass month name to measure formula

[mp390988]

Hello,

I have the below formula for my measure bascially calculates the Revenue from beginning of this year til end of previous month.

  CALCULATE(
        [Revenue],
        DATESBETWEEN(
            'CalendarTbl'[Date],
            DATE(
                YEAR(TODAY()),
                1,
                1
            ),
            EOMONTH(TODAY(),-1)
        )
    )

However, I have this slicer which I need to pass to the above formula.

So for example, if you selected February, then the above forumla should calculate the total revenue from beginning of this year til the end of Feb. 

I guess the below line of code needs replacing/updating to take in the the month selected by the slicer but I don't know how to replace this with the value selected by the slicer and also, if no value is selected in slicer then by default it should show revenue upto previous month.

EOMONTH(TODAY(),-1)​

Thank You

[v-sdhruv]

Hi @mp390988 ,

Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank You for using Microsoft Community Forum

[v-sdhruv]

Hi @mp390988 ,

Just wanted to check if you had the opportunity to review the suggestions provided by the super users?
If you need any assistance, let us know.

Thank You

[v-sdhruv]

Hi @mp390988 ,

Just wanted to check if you had the opportunity to review the suggestions provided by the super users?
If you need any assistance, let us know.

Thank You

[rajendraongole1]

Hi @mp390988 -if you are slicer is based on month or date fields. you can write the measure as below:

Revenue YTD Until Selected Month :=
VAR SelectedMaxDate =
MAX('CalendarTbl'[Date]) -- This respects slicer
VAR DefaultMaxDate =
EOMONTH(TODAY(), -1) -- Used when no slicer selection
VAR EndDate =
IF(
ISFILTERED('CalendarTbl'[Month]) || ISFILTERED('CalendarTbl'[Date]),
SelectedMaxDate,
DefaultMaxDate
)
RETURN
CALCULATE(
[Revenue],
DATESBETWEEN(
'CalendarTbl'[Date],
DATE(YEAR(TODAY()), 1, 1),
EndDate
)
)

Try the above logic and let's see . hope it works.

[FBergamaschi · Accepted solution]

You cannot only select the month but you also need to select a single year (unless you have another slicer where you have selected a year but I cannot see it in the image)

If you do have a single year selected somewhere, you can use this code (MonthColumnNr is a cloum you should have in the calendar with the month nr from 1 to 12)

VAR SelectedMonth =
    SELECTEDVALUE ( MonthColumnNr )
VAR SelectedYear =
    SELECTEDVALUE ( YearColumn )
RETURN
    CALCULATE (
        [Revenue],
        DATESBETWEEN (
            'CalendarTbl'[Date],
            DATE ( YEAR ( TODAY () ), 1, 1 ),
            IF (
                NOT ISBLANK ( SelectedMonth ),
                EOMONTH ( DATE ( SelectedYear, SelectedMonth, 1 ), 0 ),
                EOMONTH ( TODAY (), -1 )
            )
        )
    )

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI