Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rastoiyashu4
Regular Visitor

Time Period

Hi Team,

 

In power bi i need one line and stacked column chart like it should show the previous month data if the date cross 12th of every month or before 12th it should should previous two months data for example current month is april and date is 15th so it should show me last six month data i.e. Mar, Feb, Jan, Dec, Nov, Oct and if it doesn't cross 12th then it should show the months i.e. - Feb, Jan, Dec, Nov, Oct, Sept. I have written a dax query which is something like this but i am getting an error that 

'Measure'[PreviousMonths]: A table of multiple values was supplied where a single value was expected. 

 

Dax Query - 

PreviousMonths =
VAR CurrentDate = TODAY()
VAR CurrentMonth = MONTH(CurrentDate)
VAR CurrentDay = DAY(CurrentDate)

RETURN
IF(
    CurrentDay > 12,
    CALCULATETABLE(
        VALUES('Calendar'[Month]),
        FILTER(
            'Calendar',
            'Calendar'[Heriearchy] <= EOMONTH(CurrentDate, -1) && 'Calendar'[Heriearchy] > EOMONTH(CurrentDate, -7)
        )
    ),
    CALCULATETABLE(
        VALUES('Calendar'[Month]),
        FILTER(
            'Calendar',
            'Calendar'[Heriearchy] <= EOMONTH(CurrentDate, -2) && 'Calendar'[Heriearchy] > EOMONTH(CurrentDate, -8)
        )
    )
)
Calendar[Heriearchy] - Contains Date, 'Calendar[Month] - contains Month number. 
 
Please let me know what i need to change in this query. It's urgent please help.
1 ACCEPTED SOLUTION
rastoiyashu4
Regular Visitor

Hello Everyone!!
Thanks for your responses and your time, really appreciate, but i have solved the issue by my own by using the below mention DAX query. But anyways, really thanks for your support. 

SelectedMonths =
VAR CurrentDate = TODAY()
VAR CurrentDay = DAY(CurrentDate)
VAR StartDate =
    IF(
        CurrentDay > 12,
        EOMONTH(CurrentDate, -6),
        EOMONTH(CurrentDate, -5)
    )
VAR EndDate = EOMONTH(CurrentDate, -1)
VAR EndDate_1 = EOMONTH(CurrentDate, -2)
RETURN
IF(CurrentDay>12,
 (CALCULATE(
        SUMX('Measure',[Orders (MUSD)]),
        'Calendar'[Heriearchy] >= StartDate && 'Calendar'[Heriearchy] <= EndDate)),
        CALCULATE(
        SUMX('Measure',[Orders (MUSD)]),
        'Calendar'[Heriearchy] >= StartDate && 'Calendar'[Heriearchy] <= EndDate_1))
 
Once again thanks for your support.

View solution in original post

3 REPLIES 3
rastoiyashu4
Regular Visitor

Hello Everyone!!
Thanks for your responses and your time, really appreciate, but i have solved the issue by my own by using the below mention DAX query. But anyways, really thanks for your support. 

SelectedMonths =
VAR CurrentDate = TODAY()
VAR CurrentDay = DAY(CurrentDate)
VAR StartDate =
    IF(
        CurrentDay > 12,
        EOMONTH(CurrentDate, -6),
        EOMONTH(CurrentDate, -5)
    )
VAR EndDate = EOMONTH(CurrentDate, -1)
VAR EndDate_1 = EOMONTH(CurrentDate, -2)
RETURN
IF(CurrentDay>12,
 (CALCULATE(
        SUMX('Measure',[Orders (MUSD)]),
        'Calendar'[Heriearchy] >= StartDate && 'Calendar'[Heriearchy] <= EndDate)),
        CALCULATE(
        SUMX('Measure',[Orders (MUSD)]),
        'Calendar'[Heriearchy] >= StartDate && 'Calendar'[Heriearchy] <= EndDate_1))
 
Once again thanks for your support.
v-jiewu-msft
Community Support
Community Support

Hi @rastoiyashu4 ,

First of all, many thanks to @ChiragGarg2512  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

vjiewumsft_0-1713336713789.png

2.Create the new measure to filter month.

 

PreviousMonths = 
VAR CurrentDate = TODAY()
VAR CurrentMonth = MONTH(CurrentDate)
VAR CurrentDay = DAY(CurrentDate)
VAR curyear = YEAR(CurrentDate)

RETURN
IF(
    CurrentDay > 12,
    CALCULATE(
        SUMX('Table', 'Table'[Number]),
        FILTER (
            ALLEXCEPT('Table', 'Table'[Date]), 
            ('Table'[Year] = curyear
                && 'Table'[Month] < CurrentMonth) || ('Table'[Year] = curyear - 1 && ('Table'[Month] > CurrentMonth + 5 && 'Table'[Month] <= 12))
        )
    ),
    CALCULATE(
        SUMX('Table', 'Table'[Number]),
        FILTER (
            ALLEXCEPT('Table', 'Table'[Date]), 
            ('Table'[Year] = curyear
                && 'Table'[Month] < CurrentMonth - 1) || ('Table'[Year] = curyear - 1 && ('Table'[Month] > CurrentMonth + 4 && 'Table'[Month] <= 12))
        )
))

 

3.Drag the measure into the table visual and column chart. The result is shown below.

vjiewumsft_1-1713337462152.png

vjiewumsft_2-1713337505792.png

 

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ChiragGarg2512
Super User
Super User

The error might be because of Calculatetable, try Calculate instead.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.