Previous weeks calculation

DAX DAX Calculations
1

Hi,
I went through the video on the “Calculate Previous Weeks Sales - Advanced DAX in Power BI”
I used the same DAX query:

Previous week raised tickets = 
 VAR Currentweek = SELECTEDVALUE(Dates[Week Number])
 VAR CurrentYear = SELECTEDVALUE(Dates[Year])
 VAR MAXweeknumber = CALCULATE(MAX(Dates[Week Number]),ALL(dates))
   Return
     SUMX(
         FILTER(ALL(Dates),
           IF(Currentweek =1,
              Dates[Week Number]=MAXweeknumber && Dates[Year]=CurrentYear -1,
              Dates[Week Number]=Currentweek-1 &&
              Dates[Year]=CurrentYear)),
        [Created Tickets])

However, it did not work. Please could you help?

Thank you,
Liz…

2

Not sure as would need to see more, for example what the current context of the calculation is, what the data model currently is etc.

If it’s set up like the example in the tutorial it should work, but if there’s anything different potentially it won’t.

I suggest working up a demo model to add to the forum post to get this checked further.

Thanks
Sam

3

@Chukliz,

What errors are you getting? I created some random ticket data in the PBIX file below, and the following measure:

Total Tickets Created = SUM( Sales[Tickets] ), and then just used

[Total Tickets Created] in your previous week measure, and it seems to work fine.

Previous Week Tickets = 
VAR Currentweek =
    SELECTEDVALUE ( Dates[Week Number] )
VAR CurrentYear =
    SELECTEDVALUE ( Dates[Year] )
VAR MAXweeknumber =
    CALCULATE ( MAX ( Dates[Week Number] ), ALL ( dates ) )
RETURN
    SUMX (
        FILTER (
            ALL ( Dates ),
            IF (
                Currentweek = 1,
                Dates[Week Number] = MAXweeknumber
                    && Dates[Year] = CurrentYear - 1,
                Dates[Week Number] = Currentweek - 1
                    && Dates[Year] = CurrentYear
            )
        ),
        [Total Tickets Created]
    )

image
image.png1215×641 41.1 KB

Current vs Last Week Tickets.pbix (456.8 KB)

4

error%20with%20previous%20week
error with previous week.PNG705×436 22.2 KB

Hi,

I created a measure
created tickets = count (incident[IncidentID]) and then used it to create my Previous week measure. Please attached error am getting.

and here is my code:

Previous week raised tickets = 
 VAR Currentweek = SELECTEDVALUE(Dates[Week Number])
 VAR CurrentYear = SELECTEDVALUE(Dates[Year])
 VAR MAXweeknumber = CALCULATE(MAX(Dates[Week Number]),ALL(dates))
   Return
     SUMX(
         FILTER(ALL(Dates),
           IF(Currentweek =1,
              Dates[Week Number]=MAXweeknumber && Dates[Year]=CurrentYear -1,
              Dates[Week Number]=Currentweek-1 &&
              Dates[Year]=CurrentYear)),
        [Created Tickets])
5

@Chukliz,

It’s difficult to figure out what’s going on without seeing your sample data/PBIX file, but in lieu of uploading that, you may want to take a look at the following video which walks through an approach for diagnosing and fixing an almost identical problem:

6

Hi BrianJ,

Thank you for all your support. I have fixed it. The problem was on my date table. I had to create another date table and all is fine now.

Thank you team.
Liz…

7

@Chukliz,

Great – glad to hear you got it worked out.

  • Brian