Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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.
Data Protection
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    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.

    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.

     

    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.