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